Percentage when total sales is a credit
 

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

Percentage when total sales is a credit
 

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

"Wanna Learn" wrote in message
...
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

Percentage when total sales is a credit
 

I think what you are trying to do is establish the percentage discount given
when taking into account the price and actual selling price. If so this
should do it for you.

=IF(Q7<1,"",100-(Q7/M7%))

The total sale figure doesn't come into it as far as I can see.

--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"Wanna Learn" wrote:

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

Percentage when total sales is a credit
 

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)
--
Regards
Roger Govier

Wanna Learn wrote:
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