Index & Match Problem

[Mike]

Hello All,
Using Excel XP

I have a worksheet that has 30 days in B3:AE3,(Sep 1, Sep 2, etc...) each
shows the average wind direction for that certain day.
I want to find the wind direction that was used the most during that period.

Before I had the worksheet that had the days in B3:B34 and had a formula as
follows:
{=INDEX(B4:B34,MATCH(MAX(COUNTIF(B4:B34,B4:B34)),COUNTIF(B4:B34,B4:B34),0),1)}
to find the maximum direction for the 30 day period and it worked without a
problem.

In the selection of B3:AE3, I am using the formula:
{=INDEX(B3:AE3,MATCH(MAX(COUNTIF(B3:AE3,B3:AE3)),COUNTIF(B3:AE3,B3:AE3),0),1)}
which gives me #REF!

After typying the formula in, I use the Shift-Ctrl-Enter keys.

Any help would be appreciated.

Michael

 

[Bob Phillips]

=INDEX(B4:AE4,1,MATCH(MAX(COUNTIF(B4:AE4,B4:AE4)),COUNTIF(B3:AE4,B4:AE4),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Mike]

Thanks Bob for your help
looks like I left out the ,1, after the first (B4:AE4)
Mike

 

[RNC]

1. create a row from B4:AE4 with the following formula:
=SUM(($B$3:$AE$3=B$3)*1) <- crtl-alt-enter
This formula gives you count for each direction
2. In A4, put the following formula:
=INDEX(B3:AE3,1,MATCH(MAX(B4:AE4),B4:AE4,0))
This formula finds the direction based on the postion of the cell that
has the direction with the greatest frequency.

 

[Bob Phillips]

You had row/column index the wrong way around.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)