I have the following data in Excel Sheet "Sheet1":
A B
1 MY_FIELD YEAR
2 333 2010
3 354 2012
4 100 2013
5 145 2010
I am attempting to return the maximum value of MY_FIELD for each year in another sheet. In this other sheet I have
A B
1 YEAR MAX_VALUE
2 2010
3 2011
4 2012
5 2013
Into B2 I enter
=MAX(IF(Sheet1!$B$1:$B$5=A2,Sheet1!$A$1:$A$5))
with CTRL+SHIFT+ENTER to enter as an array formula.
This is returning zero. I understand this occurs if the value searched for is not found, but I can see the value searched for, A2, is 2010, which is present in the array Sheet1!$B$1:$B$5.
What is the error in my formula or my approach?
The years in Sheet1!$B$1:$B$5 were formatted using TEXT(DATE,"YYYY"). This returns text fields. The formula requires strictly numbers.
Copying the data in Sheet1!$B$1:$B$5, pasting as values, and setting all to number, returns in the formula working.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments