Multiplying unil greater than a number?

[ninj5]

Hello,
I'm trying to create a formula that will multiply a cell H4 until it is greater than another cell H3.
Then, it will take the difference, and divide it by the number it multiplied H4 by...


So if cell H3 was $118.76, and H4 was 35, it would calculate that 35*4= $140, therefore 140-118.76 is 21.24, and divide that by 4 to produce 5.31

Thoughts?
Thanks!!!

[Moo the Dog]

You could try:

=(H4-MOD(H3,H4))/(INT(H3/H4)+1)

- Moo

[Ron Coderre]

Not quite sure where this is going...but

H3: 138.5
H4: 35

This regular formula does what you're asking for:

Please Login or Register  to view this content.

Is that something you can work with?

[ninj5]

Thanks Moo for the quick response!
Although I'm not sure it worked...

I have H11 = 118.76 and H12= 35
It should have produced:

140-118.76 =21.24 /4 = 5.31
Instead of 5.31 it produced 12.935... Thoughts?

[ninj5]

Not quite sure where this is going...but

H3: 138.5
H4: 35

This regular formula does what you're asking for:

Please Login or Register  to view this content.

Is that something you can work with?

Thanks for the help! the formula you provided results in: $1.05...

I probably should have put in the correct numbers in the first post. Sorry about that...
H11 is 118.76 and H12 is 35

The formula should result in $5.31
This is because 35 times 4 is 140, and that minus H11 is 21.24. That divided by 4 is 5.31

I'm nowhere near an expert with formulas so this helps me a lot... looking up MOD and ROUNDUP now...

[Moo the Dog]

When I make the changes according to your most recent post and put 118.76 in cell H11, and 35 in H12, using this formula in any blank cell:
=(H12-MOD(H11,H12))/(INT(H11/H12)+1) comes out to 5.31

You must have typed something in incorrectly?

- Moo

[ninj5]

You could try:

=(H4-MOD(H3,H4))/(INT(H3/H4)+1)

- Moo

Hey Moo, Your first part of that formula was great! =(H4-MOD(H3,H4)) produces 21.24.
Not sure why INT(H3/H4+1) produces 2... but it should produce 4.

Gonna try and look up the INT functioning now...

[ninj5]

Hey Moo, Your first part of that formula was great! =(H4-MOD(H3,H4)) produces 21.24.
Not sure why INT(H3/H4+1) produces 2... but it should produce 4.

Gonna try and look up the INT functioning now...

Yes, you are correct! I copied and pasted instead of looking at the correct cells...
You did it!
Thanks again for your help!

[Moo the Dog]

Glad to help.

The INT function simply returns the integer (WHOLE NUMBER) value of a number. For instance, =INT(4.53) will result in 4. =INT(5/2) will result in 2.

The MOD function [=MOD(number, divisor)] returns the REMAINDER of a division formula. For instance =MOD(5,2) will result in 1, because 2 goes into 5 twice, with a remainder of 1.

Hope that helps!

- Moo

[ninj5]

Glad to help.

The INT function simply returns the integer (WHOLE NUMBER) value of a number. For instance, =INT(4.53) will result in 4. =INT(5/2) will result in 2.

The MOD function [=MOD(number, divisor)] returns the REMAINDER of a division formula. For instance =MOD(5,2) will result in 1, because 2 goes into 5 twice, with a remainder of 1.

Hope that helps!

- Moo

Yes, this helped a great deal. Thanks again!