Excel dilemma

[sboyd1350]

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?

[#2]

@sboyd1350 

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

[#3]

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.

[#4]

@sboyd1350 

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

[#5]

@sboyd1350 

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