Forum Discussion
Solved
Fuctions
Background: I work at a water utility District. For years we have manually calculated adjustments for leaks. I would like to use excel to double-check our calculations. I am not overly familiar with ...
Hi,
That could be like this
Not to hardcode your tier ranges they are in yellow cells.
Sample file is attached.
Hi Mariya,
see if this works for you..
in this example the additional "Tier cf price" column holds the prices per cf, per tier
but you can remove that and use a formula in column C,
for example cell C9 would be =B9*2,65
where 2,65 is the cf price ...
(I put "Previous Years" in cell A1)
Formula CEILING is there to remove decimals from average
| Previous Years | Water Use | Average Use | |
| 2018 | 67 | 55 | |
| 2017 | 67 | ||
| 2016 | 30 | ||
| Tiers | Adjusted | Adjusted | Tier |
| Water Use | Water Charges | cf price | |
| Tier 1 (1-10ccf) | 10 | $ 26,50 | $ 2,65 |
| Tier 2 (11-30ccf) | 20 | $ 92,20 | $ 4,61 |
| Tier 3 (31-50ccf) | 20 | $ 131,40 | $ 6,57 |
| Tier 4 (>50ccf) | 5 | $ 42,65 | $ 8,53 |
| $ 292,75 |
same sheet, with used formulas shown
| Previous Years | Water Use | Average Use | |
| 2018 | 5 | =CEILING(AVERAGE(B2:B4);1) | |
| 2017 | 5 | ||
| 2016 | 5 | ||
| Tiers | Adjusted | Adjusted | Tier |
| Water Use | Water Charges | cf price | |
| Tier 1 (1-10ccf) | =IF(C2>=10;10;C2) | =B9*D9 | 2,65 |
| Tier 2 (11-30ccf) | =IF(C2-B9>=20;20;C2-B9) | =B10*D10 | 4,61 |
| Tier 3 (31-50ccf) | =IF(C2-SUM(B9:B10)>=20;20;C2-SUM(B9:B10)) | =B11*D11 | 6,57 |
| Tier 4 (>50ccf) | =IF(C2>50;C2-SUM(B9:B11);0) | =B12*D12 | 8,53 |
| =SUM(C9:C12) |
hope this helps,
kind regards,
Charlie
Thank you Charlie, I wasn't able to successfully use your suggestion, I am confident it was user error on my part. I appreciate your taking the time to post on my inquiry.
