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

Price=GP%/(1-(GP%/100))

<tbody>
</tbody>

or in terms of cell reference

B3=A3/(1-(C3/100)) I hope this helps.

<tbody>
</tbody>

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;">

Price=GP%/(1-(GP%/100))

<tbody>
</tbody>

</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 ...