# 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?
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
0
Excel & VBA ExpertCommented:
See if the following attached sheet helps.
Calculations.xlsx
0
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
0
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
0
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.