I have solved it using (Nested IF + SUM) . . is there a better formula to solve it?

Question

Respected Experts!

Is there a more efficient formula (compared to the formula I used) to find the Total Cost?

My dilemma: My formula would get really long if there were more bins/ranges in column#A.

Thank You!

Best Wishes!

Answer 1

@MMW.... Does the URL contain a better explanation (captured in 2007; the webpage might be even older): http://www.mcgimpsey.com/excel/variablerate.html

(Caveat: That seems to be a slow link. Give it time to load.)

Thus, your table becomes:

Formulas:

C2: =B2-N(B1)

F2: =SUMPRODUCT((E2>=$A$2:$A$4)*(E2-$A$2:$A$4), $C$2:$C$4)

The addition of column C allows the SUMPRODUCT formula in column F to be normally-entered in all versions of Excel.

It is also more efficient because it removes a constant calculation. And it might be more self-explanatory.

How it works..... The first calculation (E2-A2)*C2 applies the first cost per unit to the entire amount (E2). In this case, that overstates the cost for weights above 201. So the second calculation (E2-A3)*C3 subtracts the overage for the __all__ of the remaining amount. But that still overstates the cost for weights above 301. So the third calculation (E2-A4)*C4 subtracts the additional overage for __final__ remaining amount.

PS.... You can also enter 0, 200, 300 into column A and change first term of the SUMPRODUCT to E2>$A$2:$A$4. I choose the form that fits the problem description.

Answer 2

This is called slab billing and I have covered this here - https://eforexcel.com/wp/article-31-slab-billing-calculate-income-tax-electricity-utility-bills-based-on-slabs/

Align your data as below and put following formula in E2

=SUMPRODUCT((D2>$A$2:$A$4)*(D2-$A$2:$A$4)*($B$2:$B$4-IFERROR(--$B$1:$B$3,0)))

Answer 3

This is called slab billing and I have covered this here - https://eforexcel.com/wp/article-31-slab-billing-calculate-income-tax-electricity-utility-bills-based-on-slabs/

Align your data as below and put following formula in E2

=SUMPRODUCT((D2>$A$2:$A$4)*(D2-$A$2:$A$4)*($B$2:$B$4-IFERROR(--$B$1:$B$3,0)))

Thank You @Vijay A. Verma for Your response.

I read Your article . . would have been wonderful if it explained the why.

If it is convenient for You, please explain the formula. Even though it gets the expected result, I am sorry I could not understand it.

Thank You!

Answer 4

This happens to be the first 365 function I added to my library. :>)

Answer 5

@MMW.... Does the URL contain a better explanation (captured in 2007; the webpage might be even older): http://www.mcgimpsey.com/excel/variablerate.html

(Caveat: That seems to be a slow link. Give it time to load.)

Thus, your table becomes:

Formulas:

C2: =B2-N(B1)

F2: =SUMPRODUCT((E2>=$A$2:$A$4)*(E2-$A$2:$A$4), $C$2:$C$4)

The addition of column C allows the SUMPRODUCT formula in column F to be normally-entered in all versions of Excel.

It is also more efficient because it removes a constant calculation. And it might be more self-explanatory.

How it works..... The first calculation (E2-A2)*C2 applies the first cost per unit to the entire amount (E2). In this case, that overstates the cost for weights above 201. So the second calculation (E2-A3)*C3 subtracts the overage for the __all__ of the remaining amount. But that still overstates the cost for weights above 301. So the third calculation (E2-A4)*C4 subtracts the additional overage for __final__ remaining amount.

PS.... You can also enter 0, 200, 300 into column A and change first term of the SUMPRODUCT to E2>$A$2:$A$4. I choose the form that fits the problem description.

Thank You @joeu2004 for Your response and explanation. Appreciate!

I will spend time to understand it well :-)