Jul 12 2023 04:26 AM
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 are discharged. I thought I could use the IF function but I have been unsuccessful. We normally discharge between 3,000,000 to 4,000,000 gallons a month and this data is sent to the local Municipality to calculate our total discharge fees. I am wanting to verify the fees. The cost is broken down in gallons of discharge as follows.
The first 100,000 gallons cost $10.30 per 1000 gallons.
The second 650,000 gallons cost $7.66 per 1000 gallons or >100,000 gallons but <=750,000 gallons.
The third 1,250,000 gallons cost $6.22 per 1000 gallons or >750,000 gallons but <= 1,250,000 gallons.
The fourth 30,000,000 gallons cost $ 5.25 per 1000 gallons or >1,250,000 gallons but <= 30,000,000 gallons.
As I stated above, we discharge between 3,000,000 to 4,000,000 gallons a month would I use the IF function to calculate the total cost per month or is there a better way to calculate these charges?
Jul 12 2023 05:07 AM
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
Jul 12 2023 08:43 AM
Jul 12 2023 11:44 AM
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 |
Jul 12 2023 03:25 PM
Since you have 365, this is overkill for the problem in hand.
= MAP(dischargeVolume, TieredCostλ(threshold, costPer1000))
The list 'dischargeVolume' corresponds to the the different months, threshold and costPer1000 are names given to the lookup data. The point of the lambda function is that it conceals the complexity.
= LAMBDA(dischargeVol,
LET(
limit, VSTACK(DROP(threshold, 1), ∞),
volume, MAP(threshold, limit,
LAMBDA(th, lt, MAX(MIN(lt, +dischargeVol) - th, 0))
),
SUM(volume * costPer1000) / 1000
)
)