Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula working backwards from finished price
I am stuggling with the following formula. If I have a unit that is worth
$100 in Col A and I have another column representing a 10% on the $100 and then Col. C representing a 17.5% charge on the $100 and Col D representing A+B+C this is equal to $127.50. But I want to go from Col D $127.50 to establish what Col C is at 17.5% and Col B is at 10% and therefore Col A is eqal $100. What formulas do I need to enter into Cols C,B,A . I hope this clear as I have been going crazy trying to get this. Thanks In ananticipation Eqa |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula working backwards from finished price
"Eqa" wrote...
I am stuggling with the following formula. If I have a unit that is worth $100 in Col A and I have another column representing a 10% on the $100 and then Col. C representing a 17.5% charge on the $100 and Col D representing A+B+C this is equal to $127.50. But I want to go from Col D $127.50 to establish what Col C is at 17.5% and Col B is at 10% and therefore Col A is eqal $100. .... Algebra helps. a = 100, b = a * 0.10 = 10, c = a * 0.175 = 17.50, d = a + b + c = a * (1 + 0.10 + 0.175) = a * 1.275 = 127.50. Given d = 127.50, a = d / 1.275 = 100, - A1: =D1/1.275 b = a * 0.10 = d * 0.10 / 1.275 = 10 - B1: =D1*0.1/1.275 or =A1*0.10 c = a * 0.175 = d * 0.175 / 1.275 = 17.50 - C1: =D1*0.175/1.275 or =A1*0.175 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula working backwards from finished price
Thanks Harlan I was going around and around. That is great!!!
"Harlan Grove" wrote: "Eqa" wrote... I am stuggling with the following formula. If I have a unit that is worth $100 in Col A and I have another column representing a 10% on the $100 and then Col. C representing a 17.5% charge on the $100 and Col D representing A+B+C this is equal to $127.50. But I want to go from Col D $127.50 to establish what Col C is at 17.5% and Col B is at 10% and therefore Col A is eqal $100. .... Algebra helps. a = 100, b = a * 0.10 = 10, c = a * 0.175 = 17.50, d = a + b + c = a * (1 + 0.10 + 0.175) = a * 1.275 = 127.50. Given d = 127.50, a = d / 1.275 = 100, - A1: =D1/1.275 b = a * 0.10 = d * 0.10 / 1.275 = 10 - B1: =D1*0.1/1.275 or =A1*0.10 c = a * 0.175 = d * 0.175 / 1.275 = 17.50 - C1: =D1*0.175/1.275 or =A1*0.175 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: Need Excel formula to return correct price from price history table | Excel Discussion (Misc queries) | |||
XL2000 stock price web query stopped working | Excel Discussion (Misc queries) | |||
create a formula for price * discount* tax =final price | Excel Discussion (Misc queries) | |||
formula for - price times discount times tax = final price | New Users to Excel | |||
msg box with finished time, please read | Excel Worksheet Functions |