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
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
See if the following attached sheet helps.
Calculations.xlsx
Calculations.xlsx
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks
Rob H
Floor-area-calc.xlsx