# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Would very much appreciate help in creating a formula to calculate cost based on quantities that fall within set increments. Here's the scenario. There is one row for a customer to enter the desired quantity. Column A=quantity, Column B=per unit/increment cost of \$8 (for each increment of 10 exceeding 20, as the first 20 are "free"), Column C=calculated total cost. If the customer buys any quantity within 1-20, cost is 0. If customer buys any quantity within 21-30, cost=8. If customer buys any quantity within 31-40, cost=16. And so on. EXAMPLE: If a quantity value from 1-20 is entered in Column A, the cost in Column C would be zero. For example, whether the quantity is 1 or 20 or anywhere in between, the cost would be \$0 - the first 20 are free. If a quantity value from 21-30 is entered in Column A, the cost in Column C would be \$8. For example, whether the quantity is 21 or 30 or anywhere in between, the cost would be \$8 (first 20 free, any value that falls within the next increment of 10 [21-30], would be \$8 total.) If a quantity value from 31-40 is entered in Column A, the cost in Column C would be \$16. For example, whether the value is 31 or 40 or anywhere in between, the cost would be \$16 (first 20 free, + first increment of 10 exceeding 20 [21-30] = \$8, plus additional quantity falling within the second increment of 10 [31-40]=\$8, for a total cost of \$16.) And so on.
Solved by G. Y. in 12 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 19/02/2018 - 06:50
User 19/02/2018 - 06:51
Hi, thanks for helping
Excelchat Expert 19/02/2018 - 06:52
Can you please list the breakups in the preview.
Excelchat Expert 19/02/2018 - 06:52
?
User 19/02/2018 - 06:53
yes, that's correct, but keep going up to 100
User 19/02/2018 - 06:55
but the customer won't see the table. The customer will only enter a single quantity (not a range) in A1
Excelchat Expert 19/02/2018 - 06:56
=VLOOKUP(A2,C5:E13,3,true)
Excelchat Expert 19/02/2018 - 06:57
Normally, these problems are handled after adding data in the form of the table.
Excelchat Expert 19/02/2018 - 06:57
It is easy to use it for any number of rows.
User 19/02/2018 - 06:59
you are the best! So I can just hide the table, and have to cost calculate based on the hidden table.
User 19/02/2018 - 06:59
I was making it much more difficult than it was
Excelchat Expert 19/02/2018 - 06:59
That is right. Writing nested if formula, is like crossing seven oceans.
Excelchat Expert 19/02/2018 - 06:59
:)
User 19/02/2018 - 06:59
I was trying to add If <21, then... and then if in this range....
Excelchat Expert 19/02/2018 - 06:59
So, the best approach is using this table.
User 19/02/2018 - 06:59
It was becoming a nightmare.
Excelchat Expert 19/02/2018 - 07:00
Hope, you got the answer as required.
Excelchat Expert 19/02/2018 - 07:00
Excelchat Expert 19/02/2018 - 07:00
Excelchat Expert 19/02/2018 - 07:00
Excelchat Expert 19/02/2018 - 07:01
Please leave a great review after ending the session.
User 19/02/2018 - 07:01
thank you so much for your help. I really appreciate it!
Excelchat Expert 19/02/2018 - 07:02
Thank you.

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.