Excel Formula

[Awolbert]

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.

[#2]

@Awolbert Can you share a sample workbook of your data, removing any sensitive data?

[#3]

@adversi 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? 

Thank you!

[#4]

@Awolbert 

I didn't catch you'd like to show discounted price or only discount. If the first

=IF( ($E5<=5)*($E5>0),$H5*(1-$I$2),0)

and

=IF( $E5>5,$H5*(1-$J$2),0)

and

=MAX(I5,J5)

[#5]

@Sergei Baklan 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. 

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.

[#6]

@Awolbert 

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

[#7]

@Sergei Baklan I had trouble making them work. Do I copy from =IF with the parenthesis and spaces as you have it? Is the first for column I and the second for column J? The MAX is for the extension, I see.

Again I apologize for the dumb questions.

[#8]

@Sergei Baklan I think I almost have it figured out. Actually I just changed the extension to be

=MAX(I5,J5)*E5 so I sum the quantity of the discounted price

Why does the formula for I column change the font color? and not stay in the $ format?

[#9]

@Awolbert 

Sorry, I don't know - in my copy of the file there are no format changes.

[#10]

@Sergei Baklan Where in the formula is the percentage discount subtracted from the sum? I don't see that. 5 and under is 5% discount and 6+ is 10% discount. Which part of the formula does this? I am used to *.95 and *.9 to get the discounted price.

Also when I typed the formula exactly as you have it into my REAL document, the formula works but doesn't calculate the discount mentioned above. Ugh I just don't get it. Below is my template which seems to work. Why doesn't it work when I put the formulas in my actual document??? What am I doing wrong?