Formula Help... Calculating charges based on stepped rates.

[HoosierIT]

Never tried complicated formulas in Access and at a bit of a loss... What I am trying to do is calculate a utility bill based on stepped rated.

For example:

Usage up to the first 500KHW is billed at .067 per KWH
Usage after the first 500KWH from 501 to 999 is billed at .044 per KWH
Usage from 1000 up is billed at .0318



So if my usage was 1200 KWH...

((500 x .067)+(500 x .044)+(200 x .0318)) = 61.86

I was assuming it would require an complex "if" function to split the 1200 into steps and then calculate charges per step?

[June7]

A single expression in query or textbox would be complicated. Probably should build a function to handle this.

[HoosierIT]

I actually meant to post this to an Excel forum... But if anyone happens to have knowledge of Excel...

[June7]

Could certainly do a calc in separate cells to test for each level and calculate the amount then add the 3 cells. Actually could do that in Access query as well.

[amrut]

this should get you started :
Need to separately calculate the three slabs in a query like
Slab1 : IIf([Usage]<500,[Usage]*0.67,500*0.67)
Slab2 : IIf([Usage]>500, IIf([Usage]>1000,500*0.44,([Usage]-500)*0.44),0)
Slab3 : IIf([Usage]>1000,([Usage]-1000)*0.0.318,0)
Total : Slab1 +Slab2+Slab3
You will need to fine tune the limits in above formulae.