|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
Mar 21st, 2002, 06:06 PM
|
#1 |
|
Board Regular
Join Date: Mar 2002
Location: Arkansas
Posts: 358
|
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"))))))) |
|
|
|
Mar 21st, 2002, 06:26 PM
|
#2 |
|
Board Regular
Join Date: Mar 2002
Location: Wellington
Posts: 104
|
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") |
|
|
|
|
Mar 21st, 2002, 06:31 PM
|
#3 |
|
MrExcel MVP
Join Date: Mar 2002
Location: Chicago, IL USA
Posts: 2,042
|
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 ] |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
