SOLVED

Band Billing

%3CLINGO-SUB%20id%3D%22lingo-sub-1631720%22%20slang%3D%22en-US%22%3EBand%20Billing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1631720%22%20slang%3D%22en-US%22%3E%3CP%3Ehow%20do%20set%20a%20invoicing%20for%20water%20in%20excel%20which%20is%20paid%20for%20in%20bands%20i.e%20total%20usage%20is%20100%20cubic%20metres%3C%2FP%3E%3CP%3E0-30%20cubic%20metress%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%401%20USD%20per%20Cubic%20metre%3C%2FP%3E%3CP%3E31-70%20Cubic%20metres%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%402%20USD%20per%20Cubic%20Metre%3C%2FP%3E%3CP%3E71%20and%20above%20cubic%20metres%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%403%20USD%20per%20cubic%20metre%3C%2FP%3E%3CP%3Ewere%20if%20i%20just%20key%20in%20the%20usage%20excel%20would%20done%20this%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1631720%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1631872%22%20slang%3D%22en-US%22%3ERe%3A%20Band%20Billing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1631872%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780882%22%20target%3D%22_blank%22%3E%40Kauphil%3C%2FA%3E%2C%3C%2FP%3E%3CP%3ETry%20this...%3C%2FP%3E%3CP%3E%3DIF(A2%26lt%3B31%2CA2*1%2CIF(A2%26lt%3B71%2C30%2B(A2-30)*2%2CIF(A2%26gt%3B70%2C110%2B(A2-70)*3)))%3C%2FP%3E%3CP%3ECell%20A2%20is%20where%20you%20type%20in%20the%20usage%20and%20B2%20should%20give%20you%20the%20pricing%20you%20are%20looking%20for.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1599140647984.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216313i09159F59CC81872D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1599140647984.png%22%20alt%3D%22Bennadeau_0-1599140647984.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 

4 Replies

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.

Bennadeau_0-1599140647984.png

 

Best Response confirmed by Kauphil (New Contributor)
Solution

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

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

@Bennadeau thanks for this...