You could set a table up so your solution uses lookup values. One advantage
is that editing the table may be easier that editing the formula (and you
know it will need to be edited at some point in time). Also easier to play
"what-if?".

Units Charge@ Less
0 0.10 0.00
101 0.15 5.00
201 0.20 15.00

Charges = (NumOfUnits x Dlookup( NumOfUnits, MyTable, 2))-
Dlookup(NumOfUnits,MyTable,3)

Charge for 201 units = (201 * 0.20) - 15.00 = 25.20

HTH
--
George Nicholson

Remove 'Junk' from return address.


"choo" wrote in message
...
Hi Karthik,
Thanks for the formula. And yes the result should be 17.50. Typo error
;)
Lets say if I want to create a simple table for the rates like:

a1 b1
1 <=100 0.1
2 101 to 200 0.15
3 201 and above 0.20

How should the formula look like?

"Karthik Bhat - Bangalore" wrote:

Hi Choo

A simple formula will take care of your requirement.
Suppose you have the units in cell A1 then enter the following formula
in cell B1

=IF(A1<0,0,(IF(A1<=100,A1*0.1,IF(A1<=200,10+(A1-100)*0.15,25+(A1-200)*0.2))))

And by the way the result of 150 units will be 17.5 and not 7.5. Or is
there something that I have missed...

Thanks
Karthik Bhat
Bangalore