Forum Discussion
Excel Formula Adjustment
I need help with an "IF" formula. I received tips from this community to create formulas in the attached spreadsheet but I need one more contingency formula. I am offering quantity discounts in two tiers and the formulas work well but I cannot offer the quantity discounts on custom product. Therefore if a Custom Quote number appears in column D, there will be no discount offered on Column H (so I and J would be blank, and the extension will be straightforward. See example line 10.
Is there a formula that would disallow a discount in Columns I and J IF there is a custom quote in D? Then could the extension column be adjusted as well to merely create a sum of the quantity and cost each? Thank you so much to anyone who can help.
20 Replies
Awolbert ,,
Check the attached WB and find the modified formula in Column I and J,, read the comment also.
Rajesh_Sinha Nevermind! I think I got it from a previous reply. Thank you so much. How do I change the text in columns I and J? I would like it to be blank. Dollar sign format.
Check the Column K I've edited the formula also column I where I have replaced TEXT with Zero. Hope this work for you.
Rajesh_Sinha What about the extension column K? See attached WB green highlights. Can you adjust the sum in column K to capture H, I, J multiplied by E?
- Try:
I5 =IF(($E5>0)*($E5<=5)*($D5=0),$H5*(1-$I$2),0)
J5 =IF(($E5>5)*($D5=0),$H5*(1-$J$2),0)
K5 =(D5+(D5=0)*MAX(I5,J5))*E5
