Forum Discussion

Copper Contributor

Sep 03, 2020

Solved

Band Billing

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

Formulas and Functions

Riny_van_Eekelen
Sep 03, 2020
Kauphil As a variant, perhaps the attached workbook contains a solution to your question.
kauphil.xlsx10 KB

4 Replies

Riny_van_Eekelen
Platinum Contributor
Sep 03, 2020
Kauphil As a variant, perhaps the attached workbook contains a solution to your question.
kauphil.xlsx10 KB
- Kauphil
  Copper Contributor
  Sep 04, 2020
  Riny_van_Eekelen thankyou so very much ....its 101% what i was looking for, am happy to be part of the community...
Bennadeau
Iron Contributor
Sep 03, 2020
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.
- Kauphil
  Copper Contributor
  Sep 04, 2020
  Bennadeau thanks for this...

Resources