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

HI, It's pleasure to help you today.

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

Please download a copy.

Excelchat Expert
19/02/2018 - 07:00

Have a great day ahead.

Excelchat Expert
19/02/2018 - 07:00

Please visit Got It Pro.

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