• 504K Members
• 3,748 Online
• 601K Conversations
SOLVED

New Contributor

# 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 AdjustedWater Use AdjustedWater Charges 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

5 Replies

# Re: Fuctions

It is better if you upload your sample file.
Solution

# Re: Fuctions

Hi,

That could be like this

Not to hardcode your tier ranges they are in yellow cells.

Sample file is attached.

# Re: Fuctions

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

Highlighted

# Re: Fuctions

Thank you Sergei, I was able to successfully manage these suggested functions.  I appreciate your input.

# Re: Fuctions

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies