Band Billing

[Kauphil]

how do set a invoicing for water in excel which is paid for in bands i.e total usage is 100 cubic metres

0-30 cubic metress                 @1 USD per Cubic metre

31-70 Cubic metres                @2 USD per Cubic Metre

71 and above cubic metres     @3 USD per cubic metre

were if i just key in the usage excel would done this 

[#2]

Hi @Kauphil,

Try this...

=IF(A2<31,A2*1,IF(A2<71,30+(A2-30)*2,IF(A2>70,110+(A2-70)*3)))

Cell A2 is where you type in the usage and B2 should give you the pricing you are looking for.

[#3 · Accepted solution]

@Kauphil As a variant, perhaps the attached workbook contains a solution to your question.

[#4]

@Riny_van_Eekelen thankyou so very much ....its 101% what i was looking for, am happy to be part of the community...

[#5]

@Bennadeau thanks for this...