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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

UDcc123

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,788
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

Board Regular
Joined
Feb 24, 2004
Messages
93
Thanks...I may have made my example too simplistic. What if the four % drops are all different random numbers?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,788

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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.
 

LuisV805

New Member
Joined
Jan 9, 2014
Messages
23

ADVERTISEMENT

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.
 

UDcc123

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

Well-known Member
Joined
Feb 15, 2002
Messages
4,257
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">-10%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">5%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">-8%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-bottom: 1px solid black;;">-2%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #CCFFCC;;">$85.20 </td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #CCFFCC;;">$85.20 </td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">T201903a</td><td style="text-decoration: underline;color: #0000FF;;">3a</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">3a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A6</th><td style="text-align:left">=FVSCHEDULE(<font color="Blue">A1,A2:A5</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A7</th><td style="text-align:left">=FVSCHEDULE(<font color="Blue">A1,{-0.1;0.05;-0.08;-0.02}</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top