Forum Discussion
Setting up Tiered Billing System
Calling All Excel Experts! I'm seeking help with setting up formulas for our small volunteer-run water company in drought-stricken CA, where we still have some water 'superusers'. I want to establish formulas that would apply a higher rate per cf (cubic foot) of usage over baseline, in 4 Tiers. The baseline amount is up to 6000 cf water (for 6 months), and no surcharge. The first surcharge Tier is charged for use from 6000-9000cf water, and we charge $70/cf for each cf above 6000. Next Tier is applied to 9001-11000 cf usage, charging $110/cf for each cf used over 6000. Tier 3 is for 11001-13000 cf, charging $130/cf over 6000 cf usage. Tier 4 is for any usage 13001cf and above, charging $150/cf used over 6000 cf.
I have been working with IF formulas and IF/AND formulas, but no good results yet. It's more complicated than I'd realized, and I only have rudimentary knowledge. Any help with this gratefully received!
1 Reply
dabadger Begin by creating a table with the billing tiers as in the picture below. Then the formula in H2 will calculate the surcharge. file attached.
