# Function in Excel

I need a function in Excel to perform the below calculation. Any help is appreciated.

Example 1: usage of 15m3
Usage(m³)            Rate(Afl)            Cost(Afl)
3.00      x      4.55      =      13.65
3.00      x      4.55      =      13.65
6.00      x      6.25      =      37.50
3.00      x      11.25      =      33.75
0      x      15.25      =      0
Water Meter Rental      =      1.00
Total monthly cost:Afl 99.55

Example 2: usage of 75m3
Usage(m³)            Rate(Afl)            Cost(Afl)
3.00      x      4.55      =      13.65
3.00      x      4.55      =      13.65
6.00      x      6.25      =      37.50
8.00      x      11.25      =      90.00
55.00      x      15.25      =      838.75
Water Meter Rental      =      1.00
Total monthly cost:Afl 994.55

Example 3: usage of 7.5m3
Usage(m³)            Rate(Afl)            Cost(Afl)
3.00      x      4.55      =      13.65
3.00      x      4.55      =      13.65
2.00      x      6.25      =      12.50
0      x      11.25      =      0
0      x      15.25      =      0
Water Meter Rental      =      1.00
Total monthly cost:Afl 40.80
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:
See attached.

Thanks
Rob H
Floor-area-calc.xlsx
Excel & VBA ExpertCommented:
See if the following attached sheet helps.
Calculations.xlsx
Finance AnalystCommented:
I have assumed first 3m at first rate, next 3m at second rate, next 6 at third rate, next 8 at fourth rate, remainder at fifth rate.

Amounts in usage column are calculated from usage figure put into F2.

Any reason why first 3 and second 3 have same rate? Why not just first 6 at same rate?

Thanks
Rob H
Author Commented:
The examples submitted are not what I need. Sorry for the confusion. I need a function that when I enter the m3 in cell A!, it automatically calculates the total,

By entering 15 in cell A1, the function in cell B1 should be =newCalc(a1) and should bring result 99.55
By entering 75 in cell A1, the function in cell B1 should be =newCalc(a1) and should bring result 994.55
By entering 7.5 in cell A1, the function in cell B1 should be =newCalc(a1) and should bring result 40.80
EngineerCommented:
You can try this formula

=CHOOSE(MATCH(A1,{0;3;6;12;20}),A1*4.55,(A1-3)*4.55+13.65,(A1-6)*6.25+27.3,(A1-12)*11.25+64.8,(A1-20)*15.25+154.8)+1

Experts Exchange Solution brought to you by