Question description:
This user has given permission to use the problem statement for this
blog.
Hi, i want to create a formula which allows a different calculation based on what volume is inputted. for example if a customer buys 500 units than my formula will multiply 90 per unit, however if i input 1300 units then my formula will multiply by 80 per unit, again if i input 2600, my formula will multiply by 57.14. My price-breaks are >500-<1100= multiply by 90 & >1300 - <1900 = multiply by 80 & >2600-<3500 = multiply by 57.14. I have managed to achieve this with IF for just 1 SUM but cannot work out how to get all three calculations working in 1 formula. please help :)
Solved by B. J. in 15 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
22/08/2018 - 09:01
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User
22/08/2018 - 09:01
hi
Excelchat Expert
22/08/2018 - 09:02
I see you need a single formula that considers first the no. of units sold before multiplying that no. by a certain multiple, based on the criteria you provided. I can help you with this. :)
User
22/08/2018 - 09:02
perfect
Excelchat Expert
22/08/2018 - 09:03
It would be great if you can share the file you're working on so we could enter the formula directly and as well consider the reference cells. However, if you can't share, may I kindly ask that you provide a sample data we can work with in the document preview. Thanks!
Excelchat Expert
22/08/2018 - 09:05
Okay, I see you currently have a formula in cell E3. Let me modify this one so it would work according to how you stated your concern. For this one, we will need a multiple nested IF statement. :)
User
22/08/2018 - 09:05
now i want to make C3 1900
User
22/08/2018 - 09:06
but the formula should multiply by 80
User
22/08/2018 - 09:06
not 95
Excelchat Expert
22/08/2018 - 09:06
Noted on that.
User
22/08/2018 - 09:07
and then when i enter 2600 multiply by 57.14
Excelchat Expert
22/08/2018 - 09:07
Just to clarify the conditions on your statement. If the value is within the range of 500 to 1100, it will be multiplied by 90, correct?
User
22/08/2018 - 09:07
yes
User
22/08/2018 - 09:08
1300 - 1900 = multiply by 80
Excelchat Expert
22/08/2018 - 09:08
If it's within the range of 1300 to 1900, it should be multiplied by 80 right? And if it's within 2600-3500, it should be multiplied by 57.14.
User
22/08/2018 - 09:08
perfect
Excelchat Expert
22/08/2018 - 09:08
What if the value is 1200 then?
User
22/08/2018 - 09:09
that do not matter = false
Excelchat Expert
22/08/2018 - 09:09
Noted on that. Let me work on it for a while and update you the soonest!
Excelchat Expert
22/08/2018 - 09:11
Already done. Please check cell E3 for your review. :)
Excelchat Expert
22/08/2018 - 09:11
The formula is: =IF(AND(C3>=500,C3<=1100),90,IF(AND(C3>=1300,C3<=1900),80,IF(AND(C3>=2600,C3<=3500,57.14))))*C3
Excelchat Expert
22/08/2018 - 09:11
Just reconfigured the formula a bit. :)
User
22/08/2018 - 09:12
wow, thats great
Excelchat Expert
22/08/2018 - 09:12
Basically, the formula is similar to the one you initially have. It uses a multiple IF and AND combination to produce the multiplier based on the cell C3 value. :)
Excelchat Expert
22/08/2018 - 09:12
It then multiplies this multiplier to the cell C3. :)
Excelchat Expert
22/08/2018 - 09:13
Do you have any more clarifications with the provided solution? :)
Excelchat Expert
22/08/2018 - 09:13
Btw, you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)
User
22/08/2018 - 09:13
i was on the right track :) thank you for the guidance
Excelchat Expert
22/08/2018 - 09:13
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
