Complex formula which works only when the first IF result is true??

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

Both IF-functions are missing the False-part. I think there's the problem.
E.g. if the first IF-function is false that would render the Index-function to use FALSE as the lookup array. That ain't gonna work.

Is there a way to ignore the "False" results? Or a different function to use that would work?

I tried a few things that obviously didn't work like 0 and ignore but they didn't work at all.

The formula works only when the first entry is True

Index needs a lookup array and (at least) a row number to do what it's designed for.
What should the formula do when any of the IFs returns FALSE?
May be you need a construction like IF(condition, INDEX(lookuparray, rownum,0), alternative value).
Is it possible for you to upload a cut down version of your workbook? Only need a few rows of data.

In the upload you had

Formula:

Please Login or Register  to view this content.

That was testing date (A2) against the wind direction range (Data!$O$2:$O$20000) That needs to be

Formula:

Please Login or Register  to view this content.

Also the workbook takes a very long time to calculate at workbook open as well as when re committing that formula.

As an alternative try a helper column in column U of 'Data'. The formula

Formula:

Please Login or Register  to view this content.

Then array enter this formula in B2 of 'Wind Direction' and fill down.

Formula:

Please Login or Register  to view this content.

I hoped the formula would simply ignore the false returns which worked if the first IF was true, if the first IF was false then the result was simply false.

No the first argument (INDEX(IF(Data!$C$2:$C$20000=A2,Data!$O$2:$O$20000)) does return FALSE. That simply changes the content of the resulting array. The resulting #N/As come from failure to MATCH and failure to find a MODE. That is why they do not return FALSE.

I have wrapped an IFERROR(formula,"") overcome the #N/As. Try IFERROR(formula,FALSE) if it helps.

I also changed the date formatting in column C 'Data' to match the Afrikaans formatting 'Wind Direction'. It affects nothing in the formulas. It was just a Band-Aid for my head.

You are welcome. Thank you for the feedback, kind words and the added rep.