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
Errol FarroAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Rob HensonFinance AnalystCommented:
See attached.

Thanks
Rob H
Floor-area-calc.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
See if the following attached sheet helps.
Calculations.xlsx
Rob HensonFinance 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
Errol FarroAuthor 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
Saqib Husain, SyedEngineerCommented:
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

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.

Start your 7-day free trial
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.