shorter formula

[dantb]

Running out of room,,any way to shortin this formula ?

=IF(AS13>=7,HLOOKUP(B13,$AD$13:$AL$20,8),IF(AS13>=6,HLOOKUP(B13,$AD$13:$AL$20,7),IF(AS13>=5,HLOOKUP(B13,$AD$13:$AL$20,6),IF(AS13>=4,HLOOKUP(B13,$AD$13:$AL$20,5),IF(AS13>=3,HLOOKUP(B13,$AD$13:$AL$20,4),IF(AS13>=2,HLOOKUP(B13,$AD$13:$AL$20,3),IF(AS13>=1,HLOOKUP(B13,$AD$13:$AL$20,2),"0.00")))))))

 

[BabyTiger]

You might want to try the following:

=IF(AS13>=1,HLOOKUP(B13,$AD$13:$AL$20,IF(AS13>=7,8,IF(AS13>=6,7,IF(AS13>=5,6,IF(AS13>=4,5,IF(AS13>=3,4,IF(AS13>=2,3,2))))))),"0.00")

 

[Jay Petrulis]

Hi,

Does this work for you?

=IF(AS13<1,0,HLOOKUP(B13,$AD$13:$AL$20,IF(AS13>=8,7,INT(AS13))+1))

Regards,
Jay

Or even shorter...

=IF(A13<1,0,HLOOKUP(B13,$D$13:$L$20,MIN(7,INT(A13))+1))
This message was edited by Jay Petrulis on 2002-03-21 17:36