formala help required.

[S S]

The basis of this formula is to work out room size and depending on the area
allocate a suitable sized air conditioning unit ....

In cells D7 to D10 I will have a figure between 0-45 (sq metres)

In cells E7-E10 I require an answer based on D cells

if answer between 0-15 then show 9000
if answer between 16-22 then show 12000
if answer between 23-30 then show 18000
if answer 31+ then show 24000

help appreciated for the formula to go into Cells E7-E10
thanks

 

[Bob Phillips]

=VLOOKUP(D7,{0,9000;16,12000;23,18000;31,24000},2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[S S]

Excellent! thanks for your speedy and accurate response.

 

[Guest]

in E7:

=LOOKUP(D7,{0,16,23,31},{9000,12000,18000,24000})

Copy down to E10.

HTH

 

[S S]

If the figure is less than 9000 how can I get it to show a blank
cell....thanks

 

[Bob Phillips]

How does it get to be less than 9000, 9000 was your smallest value?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[S S]

I suppose to be more accurate....... if they dont put in any figures it
currently shows 9000, and it would look better if it were a blank or zero.

For example I have set this up for 4 rooms if they only have 2 rooms then
the formula works out the required figure for those rooms, the other 2 rooms
have no figures (as they would not be required in this example) they would
show a default of 9000. I hope this explains.

 

[Ragdyer]

Do either of these help:

=LOOKUP(D7,{0,1,16,23,31;0,9000,12000,18000,24000})

=LOOKUP(D7,{0,1,16,23,31;"",9000,12000,18000,24000})

You can make the "1" as small as you like, say 0.1 or 0.001.

 

[S S]

thanks RD that works good.

Do either of these help:

=LOOKUP(D7,{0,1,16,23,31;0,9000,12000,18000,24000})

=LOOKUP(D7,{0,1,16,23,31;"",9000,12000,18000,24000})

You can make the "1" as small as you like, say 0.1 or 0.001.

 

[Ragdyer]

Appreciate the feed-back.