Forum Discussion

tryker23213's avatar
tryker23213
Copper Contributor
Jun 15, 2022

number breakdown to tiers

I am fairly new to Excel but have gotten myself through to understanding a good but. So, now I have this issue with a spreadsheet for a shared water well that I use for invoicing. Problem is that I cannot find a formula that works...this is a water use spreadsheet so say like I have used 13,550 gallons. I need to break that number down to three tiers along with three different charges, Tier 1 - to show up to 3,000 gallons, Tier 2 - 3,001 to 7,000 gallons and Tier 3 - 10,001 and up. I have Tier 1 and Tier 3 working with the whole number but haven't had luck with Tier 2. I can handle getting the charges but not the whole number tiered out.

 

Anyone able to help?

Tier 2 Error

13500 Gallons

$3.50 per thousand gallons used from 0-3,000 gallons

$5.50 per thousand gallons used from 3,001-10,000 gallons

$13.00 per thousand gallons used from 10,001 and up

 

Tier 1
=IF(G5<3000,G5, 3000)

Tier 2
=IF(AND(G5>3000,G5<10000),G5 -3000,7000)

Tier 3
=IF(G5>10000,G5 -10000,0)

Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jun 15, 2022

    tryker23213  Try this:

    Tier 1
    =MIN(G5, 3000)

    Tier 2
    =MAX(MIN(G5 -3000,7000),0)

    Tier 3
    =MAX(G5 -10000,0)

     

    FYI i believe your error was this:

    Tier 2
    =IF(G5<=3000, 0, IF( AND(G5>3000,G5<10000),G5 -3000,7000) )

    or you could simplify to either of these:

    =IF(G5<=3000, 0, IF(G5<10000 ,G5 -3000, 7000) )

    =IFS(G5<=3000, 0, G5<10000 ,G5 -3000 ,TRUE, 7000 )

Resources