Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array Function Question
Hi,
I am using an array function to lookup maximum and minimum values between to two dates (see below). The array function for MAX() works perfectly fine. However, once I substitute MIN for MAX and hit ctrl+shift+enter, the output i zero, even though this answer is wrong. (There are no observations with the value of 0 in the dataset. Nor are there blank observations.) Does anyone know why the array function with MIN doesn't work? In advance, thanks for your help. Henrik WORKS: {=MAX(Stock_Data!$C$3:$C$1616*($A2<=Stock_Data!$A$ 3:$A$1616)*($K2=Stock_Data!$A$3:$A$1616))} DOESN'T WORK {=MIN(Stock_Data!$C$3:$C$1616*($A2<=Stock_Data!$A$ 3:$A$1616)*($K2=Stock_Data!$A$3:$A$1616))} |
#2
|
|||
|
|||
It's because when the cells don't match your criteria, the formula returns
FALSE...which equates to ZERO. So as soon as you have a non-matching value...you get your minimum of zero. Try this: =MIN(IF(($A2<=Stock_Data!$A$3:$A$1616)*($K2=Stock _Data!$A$3:$A$1616),Stock_Data!$C$3:$C$1616,10^10) ) Remember to [Ctrl]+[Shift]+[Enter] to commit that array formula -- Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clock | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
Can I count in an array based on a function? | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
Grading Function Question... | Excel Worksheet Functions |