Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2139652%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2139652%22%20slang%3D%22en-US%22%3EMy%20spreadsheet%20has%20quantities%20and%20prices.%20I%20have%20just%20introduced%20two%20columns%20for%20quantity%20discounts%20for%205%20and%20under%20and%20over%205.%20I%20already%20have%20a%20price%20column%20EACH%20and%20then%20an%20extension%20formula%20based%20on%20quantity.%20I%20want%20the%20customer%20to%20see%20the%20original%20price%20and%20then%20the%20discounted%20price%20listed%20in%20either%20%E2%80%9C5%20and%20under%E2%80%9D%20or%206%2B.%20How%20do%20I%20write%20a%20formula%20for%20the%20extension%3F%20Using%20the%20price%20in%20either%20A%20or%20B%2C%20quantity%20discount.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2139652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2141066%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2141066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F968401%22%20target%3D%22_blank%22%3E%40Awolbert%3C%2FA%3E%26nbsp%3BCan%20you%20share%20a%20sample%20workbook%20of%20your%20data%2C%20removing%20any%20sensitive%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2143362%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2143362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3E%26nbsp%3BAttached%20is%20a%20template%20of%20the%20document%20for%20your%20review.%20Two%20things%3A%3C%2FP%3E%3CP%3E1.%20Can%20you%20fill%20either%20I%20or%20J%20based%20on%20the%20quantity%3F%20Instead%20of%20manually%20calculating%20each%20row%20based%20on%20the%20quantity%2C%20I%20wonder%20if%20the%20discounted%20price%20can%20autofill%20based%20on%20a%20formula%20to%20fill%20either%20I%20or%20J%20depending%20on%20the%20quantity.%3C%2FP%3E%3CP%3E2.%20Can%20the%20extension%2C%20column%20K%2C%20be%20calculated%20based%20on%20data%20in%20either%20I%20or%20J%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor
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.
9 Replies

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

@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!

@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)

 

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

 

 

@Awolbert 

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

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

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

@Awolbert 

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

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