First time poster and desperate for help!
I work in sales spend a lot of my time working out selling prices for my team and me. As a sanity check, I use a spreadsheet (I call my Margin Calculator) to enter all costs and then, depending on the client, the final selling price rather than pen and paper. My Excel skills are good enough for this basic task. But, and here it comes, a former colleague set up a formula for me to reverse calculate the margin, which has somehow been deleted out of my master copy and now I, for the life of me, cannot work out how the former colleague did the formula.
In my Margin Calculator I have one formula to work out margin (in percentage):
=1-(1/D3)
D3 is the cell where I add the markup, e.g.
1.25 = 20%
1.30 = 23% etc
I used to have a formula to do the reverse calculation, i.e. a cell where I could enter a percentage value and the cell below would generate the markup for me, e.g.
I would put 20% into cell D7, and the formula in cell D6 would display 1.25.
I know this should not be this hard, but I am stumped and have other members of my sales team breathing down my neck for an updated spreadsheet that works.
Please help!! What is the formula??
Thanks!
I work in sales spend a lot of my time working out selling prices for my team and me. As a sanity check, I use a spreadsheet (I call my Margin Calculator) to enter all costs and then, depending on the client, the final selling price rather than pen and paper. My Excel skills are good enough for this basic task. But, and here it comes, a former colleague set up a formula for me to reverse calculate the margin, which has somehow been deleted out of my master copy and now I, for the life of me, cannot work out how the former colleague did the formula.
In my Margin Calculator I have one formula to work out margin (in percentage):
=1-(1/D3)
D3 is the cell where I add the markup, e.g.
1.25 = 20%
1.30 = 23% etc
I used to have a formula to do the reverse calculation, i.e. a cell where I could enter a percentage value and the cell below would generate the markup for me, e.g.
I would put 20% into cell D7, and the formula in cell D6 would display 1.25.
I know this should not be this hard, but I am stumped and have other members of my sales team breathing down my neck for an updated spreadsheet that works.
Please help!! What is the formula??
Thanks!
