Dumb easy multiplication question

[UDcc123]

My brain's gone blank for some reason. Say I have $100 in cell A1. And then I have -10% in cells A2, A3, A4, and A5. I want to know what happens if I take 10% off the $100 ($90)...and 10% off of that ($81), and so forth so I end up with $65.61 after taking four 10%s off.

I can keep a running total in column B, but I was hoping to do it in one cell with one formula. I can't say: A1 * (1+SUM(A2:A5)) because that returns $60 instead of $65.61. I'm drawing a blank on how to do this in excel.

Thanks

$100
-10% $90
-10% $81
-10% $73
-10% $65.61

 

[UDcc123]

Sorry...realize I posted this to the wrong board...not sure how to delete it, but I reposted this to the more used general excel board.

 

[mikerickson]

If you have 100 in A1 and .1 in A2 and 4 in A3

=100*((1-A2)^A3)

will return the value after takind a 10% discount four times.

 

[UDcc123]

Thanks...I may have made my example too simplistic. What if the four % drops are all different random numbers?

 

[mikerickson]

I think that A1*(RAND()^4) would return the same value (over time) as if you had four different cells and did a RAND() for each line

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

$ 100.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

15%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

19%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

20%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

11%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#CCFFCC"]

$ 49.02​

[/td][td="bgcolor:#CCFFCC"]A6: {=A1 * PRODUCT(1 - A2:A5)}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#CCFFFF"]

$ 49.02​

[/td][td="bgcolor:#CCFFFF"]A7: =A1 * PRODUCT(INDEX(1 - A2:A5, 0))[/td][/tr]
[/table]

Use the formula in A6 if you're comfortable with an array formula, otherwise the formula in A7.

 

[LuisV805]

Can you use this?

[TABLE="width: 234"]
<colgroup><col width="78" span="3" style="width:59pt"> </colgroup><tbody>[TR]
[TD="class: xl73, width: 78"][/TD]
[TD="class: xl73, width: 78"]A[/TD]
[TD="class: xl73, width: 78"]B[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 78"]1[/TD]
[TD="class: xl73, width: 78"]Percent[/TD]
[TD="class: xl73, width: 78"]Balance[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 78"]2[/TD]
[TD="class: xl73, width: 78"][/TD]
[TD="class: xl75, width: 78"]100[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 78"]3[/TD]
[TD="class: xl74, width: 78"]10%[/TD]
[TD="class: xl75, width: 78"]90[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 78"]4[/TD]
[TD="class: xl74, width: 78"]10%[/TD]
[TD="class: xl75, width: 78"]81[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 78"]5[/TD]
[TD="class: xl74, width: 78"]10%[/TD]
[TD="class: xl75, width: 78"]72.9[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 78"]6[/TD]
[TD="class: xl74, width: 78"]10%[/TD]
[TD="class: xl75, width: 78"]65.61[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 78"]7[/TD]
[TD="class: xl74, width: 78"]10%[/TD]
[TD="class: xl75, width: 78"]59.049[/TD]
[/TR]
</tbody>[/TABLE]

Formula for B3:
=B2-(B2*A3)

Copy that down.

 

[UDcc123]

Thank you for the responses. The index formula roughly worked if I have all the same signs (like four -10%s)...but what if the four inputs are -10%, 5%, -8%, and -2%. I can do the running total method easily enough, but my post was to try and find an easier way in just one cell without the running formula.

In the example below, I'm trying to get the answer of $85.20 that shows with the running total in B5...but when I use the product formula in cell B6, it returns $84.88 instead of $85.20.


A B
1 $100
2 -10% $90.00
3 5% $94.50
4 -8% $86.94
5 -2% $85.20
6 $84.88

 

[Dave Patton]

try =FVSCHEDULE(A1,A2:A5)

 

[Dave Patton]

Excel 2010
	A	B
1	100
2	-10%
3	5%
4	-8%
5	-2%
6	$85.20
7	$85.20
8
9	T201903a	3a
10
3a

Cell Formulas
Range	Formula
A6	=FVSCHEDULE(A1,A2:A5)
A7	=FVSCHEDULE(A1,{-0.1;0.05;-0.08;-0.02})