Excel - IF Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc