Forum Discussion
Excel dilemma
Hello, I currently use Microsoft 365 on the Microsoft 10 platform and I need to verify the total cost for our water discharge to the POTW. This cost is broken down into the number of gallons that ar...
Your description is inconsistent, for example
"The third 1,250,000 gallons cost $6.22 per 1000 gallons or >750,000 gallons but <= 1,250,000 gallons."
From 750,000 to 1,250,000 is 500,000 and not 1,250,000.
So you may have to change the values shown below.
I'd use a lookup table:
You can then use a SUMPRODUCT formula:
The formula in C2 is
=SUMPRODUCT((B2>$E$2:$E$5)*(B2-$E$2:$E$5),$G$2:$G$5)/1000
Thank you, Hans, I understand your point but the municipality charges us a cumulative fee. What I mean is, as I stated the first 100,000 is charged then the next 650,000, and finally the next 1,250,000. These three flows are added together or 2,000,000 and anything above the 2,000,000 is charged $5.25. Does that now make sense? Yes, I know it's confusing but that is how they make their money.
That was very unclear (to me) from your first post.
Change the lookup range to
Threshold Cost per 1000 Difference 0 $10.30 $10.30 100,000 $7.66 -$2.64 750,000 $6.22 -$1.44 2,000,000 $5.25 -$0.97 Formulas:
Threshold Cost per 1000 Difference 0 10.3 =F2 100000 7.66 =F3-F2 750000 6.22 =F4-F3 2000000 5.25 =F5-F4
