Forum Discussion

Awolbert's avatar
Awolbert
Brass Contributor
Feb 16, 2021

Excel Formula

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 o...
Formulas and Functions
adversi's avatar
adversi
Iron Contributor
Feb 17, 2021

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

  • Awolbert's avatar
    Awolbert
    Brass Contributor
    Feb 17, 2021

    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!

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 17, 2021

      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)

       

      • Awolbert's avatar
        Awolbert
        Brass Contributor
        Feb 22, 2021

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

Resources