Another MATCH and INDEX problem

[Mike]

Hello All,
Using Excel XP.

I have a 31 worksheets in a workbook that are the days of month of October.
In each sheet I have a 24 hour period of certain weather data, such as
temps, humidity, winds, etc.
In another worksheet I have summary pages that show all the days of the
month (cells A1:AE1) and weather data for the 24 hour period (cells A2:A32,
A3:AE3, ...) The formula I use on the summary page is
=Oct1!A1, etc which returns the value in the Oct1 sheet from cell A1.

On the summary page, all the data from the 31 worksheets show the data and
if the day hasn't arrived yet it shows a blank cell for most weather data.
If the data is temperature then it shows the blank for the cells where the
date hasn't arrived. When the data is wind direction (N, NW, S, etc) the
cell shows up as a 0 (zero). When I look for the wind direction that shows
up the most in cells A2::AF2 (by using the INDEX and MATCH formula)
=INDEX(A2:AE3,1,MATCH(MAX(COUNTIF(A2:AE2,A2:AE2)),COUNTIF(A2:AE2,A2:AE2),0))
it shows as 0 (zero) becuase there are 21 zeroes for October (for the days
that have not arrived yet). If I hide the 0's on the page, then the average
shows up as blank because there are 21 blanks.
How can I get the Index and Match formula to count the maximun times a wind
direction shows up in A2:AE2, WITHOUT counting the 0's and/or blanks.

Thanks for any help.

Mike

 

[daddylonglegs]

One possibility would be

=INDEX(A2:AE2,MODE(IF(A2:AE2<>0,MATCH(A2:AE2,A2:AE2,0))))

confirmed with CTRL+SHIFT+ENTER

.....although there needs to be at least 2 identical wind directions for a
result other than #N/A. Of course you may have a tie, in which case it will
show the direction that appears first