number breakdown to tiers

[tryker23213]

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)

[#2]

@tryker23213 

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

You can try this formula for Tier2.

 

[#3]

@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 )