Excel Formula

Question

My spreadsheet has quantities and prices. I have just introduced two columns for quantity discounts for 5 and under and over 5. I already have a price column EACH and then an extension formula based on quantity. I want the customer to see the original price and then the discounted price listed in either “5 and under” or 6+. How do I write a formula for the extension? Using the price in either A or B, quantity discount.

adversi · Answer

Awolbert&nbsp;Can you share a sample workbook of your data, removing any sensitive data?

awolbert · Answer

adversi&nbsp;Attached is a template of the document for your review. Two things:1. Can you fill either I or J based on the quantity? Instead of manually calculating each row based on the quantity, I wonder if the discounted price can autofill based on a formula to fill either I or J depending on the quantity.2. Can the extension, column K, be calculated based on data in either I or J?&nbsp;Thank you!

sergeibaklan · Answer

Awolbert&nbsp;
I didn't catch you'd like to show discounted price or only discount. If the first
=IF( ($E5&lt;=5)*($E5&gt;0),$H5*(1-$I$2),0)

and

=IF( $E5&gt;5,$H5*(1-$J$2),0)

and

=MAX(I5,J5)
&nbsp;

awolbert · Answer

SergeiBaklan&nbsp;Thank you, Sergei! I want to show the net price and then the discounted price. I think it is important for the customer to know they are getting a quantity discount and to not lose sight of the net price that most distributors pay. That said I want to show net, then discounted price and finally the extension of the price based on the quantity specified.&nbsp;So do I use the first or second formula for columns I and J? I am sorry but I am not that Excel savvy. It may be a dumb question.&nbsp;&nbsp;

sergeibaklan · Answer

Awolbert&nbsp;
If I understood you correctly - yes, you may use formulas as in columns I in J in the file attached to previous post.

Forum Discussion

Excel Formula

9 Replies

Resources