Feb 16 2021 08:27 AM
Feb 16 2021 04:19 PM
@Awolbert Can you share a sample workbook of your data, removing any sensitive data?
Feb 17 2021 08:16 AM
@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!
Feb 17 2021 12:06 PM
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)
Feb 17 2021 01:08 PM
@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.
Feb 18 2021 05:27 AM
If I understood you correctly - yes, you may use formulas as in columns I in J in the file attached to previous post.
Feb 18 2021 06:01 AM
@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.
Feb 18 2021 06:17 AM
@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?
Feb 18 2021 07:20 AM
Sorry, I don't know - in my copy of the file there are no format changes.
Feb 22 2021 08:29 AM
@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?