Dumb easy multiplication question

UDcc123

Board Regular
Joined
Feb 24, 2004
Messages
93
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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.
 
Upvote 0
Thanks...I may have made my example too simplistic. What if the four % drops are all different random numbers?
 
Upvote 0
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
 
Upvote 0
A​
B​
1​
$ 100.00​
2​
15%​
3​
19%​
4​
20%​
5​
11%​
6​
$ 49.02​
A6: {=A1 * PRODUCT(1 - A2:A5)}
7​
$ 49.02​
A7: =A1 * PRODUCT(INDEX(1 - A2:A5, 0))

Use the formula in A6 if you're comfortable with an array formula, otherwise the formula in A7.
 
Upvote 0
Can you use this?

AB
1PercentBalance
2100
310%90
410%81
510%72.9
610%65.61
710%59.049

<colgroup><col width="78" span="3" style="width:59pt"> </colgroup><tbody>
</tbody>

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

Copy that down.
 
Upvote 0
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
 
Upvote 0

Excel 2010
AB
1100
2-10%
35%
4-8%
5-2%
6$85.20
7$85.20
8
9T201903a3a
10
3a
Cell Formulas
RangeFormula
A6=FVSCHEDULE(A1,A2:A5)
A7=FVSCHEDULE(A1,{-0.1;0.05;-0.08;-0.02})
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top