Forum Discussion

sboyd1350's avatar
sboyd1350
Copper Contributor
Jul 12, 2023

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...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 12, 2023

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

 

Resources