Hello.
Does CSE Formula work in Excel 2003?
I followed the instructions, trying to use a MINIF formula, but all I got as a result is #NUM!.
I wrote the formula as follows:
=MIN(IF($T:$T=$W2,$L:$L))
where $W2 is the constant I'm looking for in $T:$T and $L:$L holds the numeric values.
Am I doing something wrong?
Thanks.
Eyal.
On 2003, CSE formulas are not allowed to reference whole columns. That's why you get #NUM!. This limit has been lifted up on 2007 version and later.
On 2003 you need to use definite ranges or dynamic (named) ranges...
1] Definite ranges...
=MIN(IF($T$2:$T$400=$W2,$L$2:$L$400))
2] Dynamic ranges...
Local:
In W1 enter...
=MATCH(9.99999999999999E+307,L:L)
The formula becomes, still CSE...
=MIN(IF($T$2:INDEX($T:$T,$W$1)=$W2,$L$2:INDEX($L:$L,$W$1)))
Global:
Assuming that the data is on Sheet1...
Define
Lrow by means of Insert | Name | Define as referring to:
=MATCH(9.99999999999999E+307,Sheet1!$L:$L)
Define
Trange (or any other more convenient name) as referring to:
=Sheet1!$T$2:INDEX(Sheet1!$T:$T,Lrow)
and
Lrange as referring to:
=Sheet1!$L$2:INDEX(Sheet1!$L:$L,Lrow)
The formula now becomes, still CSE...
=MIN(IF(Trange=$W2,Lrange))
These names can be referred to from anywhere in the current workbook.
An additional option...
Convert the current area with headers into a List by means of Data | List | Create List and let the formula simply refer to the current ranges. The List functionality adjust the formula automatically whenever the data area itself changes.