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 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 |
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.
Hi,
That could be like this
Not to hardcode your tier ranges they are in yellow cells.
Sample file is attached.
Thank you Sergei, I was able to successfully manage these suggested functions. I appreciate your input.
- It is better if you upload your sample file.
