View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Mike H Mike H is offline
external usenet poster
 
Posts: 11,501
Default Adding numbers by limit

Hi,

heres the first 3 I think you should be able to follow the logic

=MIN(A1*0.2,400)
=MAX(0,MIN((A1-2000)*0.22,440))
=MAX(0,MIN((A1-4000)*0.24,480))

The max bits in 2 and 3 stop it returning negative values.

Mike
"Dubai Dave" wrote:

Hi

I need to carry out an energy audit and would like to use a spreadsheet to
find costs

eg
1 - 2000 kWh = 0.20
2001 - 4000 kWh = 0.22
4001 - 6000 kWh = 0.24 and so on

so that when I get a figure of say 4200 and enter in the first column it
will show the first 2000 units at 0.20 in the next column then 2001 - 4000 at
0.22 in the next column and the remaining 200 units at 0.24 in the last
column. There are other limits to add but I could add as required providing I
get the basic idea???