SOLVED

Fuctions

mariyalk55
New 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.
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. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies