Index & Match Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 08 Oct 2006 16:26 GMT

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

Reply to this Message

Bob Phillips - 08 Oct 2006 16:41 GMT

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

Signature

HTH

Bob Phillips

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

> Hello All,
> Using Excel XP
[quoted text clipped - 5 lines]
> 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!
>
[quoted text clipped - 3 lines]
>
> Michael

Reply to this Message

Mike - 08 Oct 2006 16:50 GMT

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

> =INDEX(B4:AE4,1,MATCH(MAX(COUNTIF(B4:AE4,B4:AE4)),COUNTIF(B3:AE4,B4:AE4),0))
>
[quoted text clipped - 28 lines]
>>
>> Michael

Reply to this Message

Bob Phillips - 08 Oct 2006 18:42 GMT

You had row/column index the wrong way around.

Signature

HTH

Bob Phillips

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

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

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

> >> Hello All,
> >> Using Excel XP
[quoted text clipped - 8 lines]
> > 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!
[quoted text clipped - 4 lines]
> >>
> >> Michael

Reply to this Message

RNC - 08 Oct 2006 16:56 GMT

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.

> Hello All,
> Using Excel XP
[quoted text clipped - 18 lines]
>
> Michael

Reply to this Message

Rate this thread: