Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apply various percentage with sales between a & b, b & c, etc. | Excel Worksheet Functions | |||
Enter credit (negative number) in sales template | Excel Discussion (Misc queries) | |||
formula to calculate sales tax from total sales | Excel Worksheet Functions | |||
backing sales tax out of a total including sales tax | Excel Worksheet Functions | |||
percentage of sales | Excel Worksheet Functions |