Hi guys,
I am trying to sort weather data to allow charting and have hit a wall with getting the prevailing wind direction for a day.
{=INDEX(IF(Data!$C$2:$C$20000=A158,Data!$O$2:$O$20000),MODE(IF(Data!$C$2:$C$20000=A158,MATCH(Data!$O$2:$O$20000,Data!$O$2:$O$20000,0))))}
This formula only works when the "IF" function is true for the first value it tests otherwise it returns a False in the cell. I have verified this by selecting different dates and testing the formula.
How I think it works.....
MODE: most common number IF column C (the date logged) in the data worksheet equals the date entered into cell A158, IF TRUE; MATCH: Worksheet data column "O" is the wind direction which is logged every 30 minutes (48 values for the day, totals over 16000 rows so for ease I used 20000). The MATCH is to convert the characters to a number so the MODE function will work.
The INDEX is used to convert the numbers back to the wind direction values.
I feel I may have over complicated the formula but I am quite the novice with only the holy bible (dummies guide to.....) and the web at my fingertips.
I hope I've been clear and if I have not please ask for more info/screenshots.
P.S. I'm using Excel 2010
Thanks in advance
Pic 1.JPG
Data worksheet.JPG
Bookmarks