Feb 15 2019 09:03 AM
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 functions where there is a long string of “if, then” scenarios. I am comfortable with SUM, AVG, and the more simplistic options. I know I can get excel to work for me, but I am stuck at HOW.
We calculate a 3 year average usage and based on that we separate the usage into tiers (1-10, 11-30, 31-50, 50+) for cost/charge.
I would like the Adjusted Water Use column to FILL in based on the Average Use Cell.
Ex/ Avg Use is 14 and so if the Ave Use is over 10, that 10 would fill in Tier 1, 11-30 would fill in Tier 2, etc.
Leak Adjustment Calculation | ||
Previous Years | Water Use | Average Use |
2018 | 12 | 14 |
2017 | 14 | |
2016 | 16 | |
Tiers | Adjusted | Adjusted |
Tier 1 (1-10ccf) | 10 | $26.50 |
Tier 2 (11-30ccf) | 4 | $18.44 |
Tier 3 (31-50ccf) |
| $0.00 |
Tier 4 (>50ccf) |
| $0.00 |
$44.94 |
Feb 16 2019 02:41 AM
SolutionHi,
That could be like this
Not to hardcode your tier ranges they are in yellow cells.
Sample file is attached.
Feb 16 2019 03:14 AM
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
Feb 19 2019 03:27 PM
Thank you Sergei, I was able to successfully manage these suggested functions. I appreciate your input.
Feb 19 2019 03:29 PM
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.
Feb 16 2019 02:41 AM
SolutionHi,
That could be like this
Not to hardcode your tier ranges they are in yellow cells.
Sample file is attached.