Can I get some help with this formula? (Really a math question)

[Peter h]

I can drag a formula down a column to calculate what I want, but I want it to calculate based on a number entered into a cell, rather than having a column full of simple calculations.

I'm trying to increase a base number by 25% based on a given number. I know that doesn't make much sense, so here's the explanation.

	A	B	B
1	Number	Formula	Result
2	1 (Base number)		25
3	2	=Round(B2+(B2*.25),0)	31
4	3	=Round(B3+(B3*.25),0)	39
5	4	=Round(B4+(B4*.25),0)	49
6	5	=Round(B5+(B5*.25),0)	61

<tbody>
</tbody>

Instead of this, I want to have a formula in Cell C1 that will take a number that is entered in Cell D1, and calculate the same result. I've tried:

=Round(B2+((B2*0.25)*(D1-1)),0)

But it isn't quite right. So, I'm having problems getting the math correct. Can anyone help with this?

 

[Scott Huish]

What do all the numbers in Column A have to do with this?
What number are you entering in D1 to get these results?

BTW, you don't need to reference the cell twice, just multiply by 1.25

=ROUND(B2*1.25,0)

 

[Eric W]

First, your original B3 formula can be simplified a bit to:

=ROUND(B2*1.25,0)

Second, what I think you're looking for is:

=ROUND($B$2*1.25^(D2-1),0)

which returns the same values as B3:B6 as you vary D2 from 1 to 5. However, keep in mind that when you have your iterated formulas from B2:B6, the ROUND is evaluated at each step, whereas if you use the single formula, the ROUND is only evaluated once. This means at some point, the values created by your B formulas and the value from this formula will start to diverge (when D2 = 17 in this case).

 

[Peter h]

The numbers in "A" can represent levels. and the Result in Column "B" that I'm trying to calculate can represent a max number of units needed to proceed to the next level. So for the first level, once you have 25 units, you are then at level 2. In order to move to level 3 you need 31 units, so on and so forth. So I dragged a formula down to increase the max number of units needed by 25% of the previous number of units. But instead of dragging the formula down hundreds of row, I thought I could come up with a formula that would calculate it based on the level number entered into Cell D1.

Also, thank you for the reminder about multiplying by 1.25 to get the percentage increase. I forgot all about that. lol.

 

[Peter h]

First, your original B3 formula can be simplified a bit to:

=ROUND(B2*1.25,0)

Second, what I think you're looking for is:

=ROUND($B$2*1.25^(D2-1),0)

which returns the same values as B3:B6 as you vary D2 from 1 to 5. However, keep in mind that when you have your iterated formulas from B2:B6, the ROUND is evaluated at each step, whereas if you use the single formula, the ROUND is only evaluated once. This means at some point, the values created by your B formulas and the value from this formula will start to diverge (when D2 = 17 in this case).

Thanks. That's exactly what I was looking for. What does the "^" symbol mean? Is that for exponent?

 

[Eric W]

Yes, that's the exponentiation operator. Glad we could help.