sales table question

[Keyss]

Hi,

I am after formulas for 2 cells in a spreadsheet.

Column A is a Cost price (derived from a report)
Column B is for a Sell Price
Column C is for the GP%

I want to be able to enter an amount into Column B that will give Column C the GP%. Also the reverse so that I change the figure in Column C it will adjust the Sell price in Column B.

Can anybody help with this?

 

[James006]

Hi,

Do you have already designed your basic formulas ...?

Are you familiar with event macros ...?

HTH

 

[Keyss]

Hi James006

No, I have never heard of Event macros!?

Basic formulas
To calculate GP% the formula I use is
GP%=(Price-Cost)/Price*100 or
in terms of cell reference C2=(B2-A2)/B2*100

To calculate Price the formula I use is
[TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]Price=GP%/(1-(GP%/100))[/TD]
[/TR]
</tbody>[/TABLE]
or in terms of cell reference [TABLE="width: 113"]
<tbody>[TR]
[TD="width: 150"]B3=A3/(1-(C3/100))

I hope this helps.


[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

Do you have already designed your basic formulas ...?

Are you familiar with event macros ...?

HTH

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]Price=GP%/(1-(GP%/100))

[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[James006]

Hello,

If I am not mistaken ... looks like you have already got an answer at Ozgrid ... along with a test file ...

https://www.ozgrid.com/forum/forum/help-forums/excel-formulas/1210931-sales-table-formula

 

[Keyss]

Hi James006

yes, you are correct. i wasn't sure where to start and thinking i might get a couple of answers so one might work.

Thank you for your assistance as well.

 

[James006]

You are welcome ...