SOLVED

Fuctions

Copper Contributor

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
Water Use

Adjusted
Water 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
It is better if you upload your sample file.
best response confirmed by mariyalk55 (Copper Contributor)
Solution

Hi,

 

That could be like this

image.png

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 YearsWater UseAverage Use 
20186755 
201767  
201630  
    
    
TiersAdjustedAdjustedTier
Water UseWater Chargescf 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 YearsWater UseAverage Use 
20185=CEILING(AVERAGE(B2:B4);1) 
20175  
20165  
    
    
TiersAdjustedAdjustedTier
Water UseWater Chargescf price
Tier 1 (1-10ccf)=IF(C2>=10;10;C2)=B9*D92,65
Tier 2 (11-30ccf)=IF(C2-B9>=20;20;C2-B9)=B10*D104,61
Tier 3 (31-50ccf)=IF(C2-SUM(B9:B10)>=20;20;C2-SUM(B9:B10))=B11*D116,57
Tier 4 (>50ccf)=IF(C2>50;C2-SUM(B9:B11);0)=B12*D128,53
  =SUM(C9:C12) 

 

 

hope this helps,

 

kind regards,

 

Charlie

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

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. 

1 best response

Accepted Solutions
best response confirmed by mariyalk55 (Copper Contributor)
Solution

Hi,

 

That could be like this

image.png

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

Sample file is attached.

View solution in original post