Percentage when total sales is a credit

[Wanna Learn]

Hello
In excel 2003 I have the following columns
Price Selling price total sale units sold
2914.00 2409.00 4818.00 2
This is my formula to give the % discount =-1*(Q7/R7-M7)/M7
answer is 17.33% discount
However
There are times that the total sales is a credit(e.g. product was returned)
How do I correct the formula so that when the total sales is a credit I get
blank cell

 

[Fred Smith]

It would help if you told us what was in M7, R7 and Q7.

To check for credits use:
=if(SellingPrice<0,0,(ListPrice-SellingPrice)/ListPrice)

Regards,
Fred

 

[Roger Govier]

Hi

I assume that Q7 is Total Sale Value, R7 is Units Sold, and M7 is
Selling Price. You don't say what reference is for Price - perhaps N7?

If you are not reducing the price, when more items are sold, then the
discount is simply
=IF(Q7<0,"",1-(N7/M7))
Format the cell as %

If, however, the selling price is varied according to the number of
units sold, then your formula would be
=IF(Q7<0,"",1-(Q7/R7)/M7)