I have a workbook with about 10 worksheets. Each worksheet represents a different piece of equipment. We routinely test each piece of equipment for microbial contamination. Each sample is coded to match the result to a specific piece of equipment. We have over 4000 data points from 3 different years: 2014, 2015, 2016. All data for all equipment is posted on one main spreadsheet. Based upon the sample code (a 22 character value) the result is copied onto the respective equipment's spreadsheet. Because of this, there are many blanks in the results column on each sheet. I use the COUNT function to determine the number of results by year. I want to have columns on each sheet that lists the individual results by year for the specific piece of equipment without blanks. We perform statistical analysis of the data by year to establish trends. I have managed to get all values in one column without blanks. However, I cannot get the array to list values by specific year. I have tried the following formulas:
=LARGE(IF(YEAR(b7:b5000)=2015,C7:C5000,{1;2;3;4;5}))
=IF(YEAR(B7:B5000)=2015,LARGE(C7:C5000,{1;2;3;4;5}))
=LARGE(C7:C5000,IF(YEAR(B7:B5000)=2015,{1;2;3;4;5}))
There are actually many more data points for each year, but I wanted to keep the formulas short for this purposes.
What I get is a column of the top 5 largest values regardless of year. Sometimes I get a #NUM! error; sometimes I get a FALSE error;
The date in column B is in the format dd/mm/yyyy, formatted as a date (I think it is referred to as a Short Date); The values in column C are formatted as General; I have a cell that contains the year, formatted as General, but for this purpose I typed in the date. I select the formula and press CTRL+Shift+Enter. That generates the top values from column C correctly, but regardless of year. When I change 2015 with 2016 I get the same values, even though the data for 2016 is quite different.
Another issue: it appears that the results are dependent on how the dates are sorted on the Main page. If sorted newest to oldest (year 2016 is first) then I get the top values when I enter 2016 into the formula, but I get the #NUM! error for 2015. If I sort date oldest to newest, the exact reverse happens: 2016 now has the #NUM! error, but 2015 produces the top 5 largest values regardless of year.
I figure it has to do with the format or syntax of the YEAR function and where it is positioned in the formula, or these functions (I have tried SMALL and LARGE) cannot accommodate sorting by year.
This is very frustrating. Any suggestion?
Bookmarks