Index & Match Problem

```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.

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

```
 0
windme (104)
10/8/2006 3:26:41 PM
excel 39879 articles. 2 followers.

4 Replies
850 Views

Similar Articles

[PageSpeed] 45

```=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" <windme@cox.net> wrote in message
> 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.
>
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
>
>
>
>
>
>

```
 0
bob.NGs1 (1661)
10/8/2006 3:41:09 PM
```Thanks Bob for your help
looks like I left out the ,1, after the first (B4:AE4)
Mike

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:ewLJ5Av6GHA.4404@TK2MSFTNGP04.phx.gbl...
> =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" <windme@cox.net> wrote in message
>> 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.
>>
> 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
>>
>>
>>
>>
>>
>>
>
>

```
 0
windme (104)
10/8/2006 3:50:53 PM
```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.

Mike wrote:
> 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

```
 0
10/8/2006 3:56:28 PM
```You had row/column index the wrong way around.

--
HTH

Bob Phillips

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

"Mike" <windme@cox.net> wrote in message
> Thanks Bob for your help
> looks like I left out the ,1, after the first (B4:AE4)
> Mike
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:ewLJ5Av6GHA.4404@TK2MSFTNGP04.phx.gbl...
> >
=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" <windme@cox.net> wrote in message
> >> 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.
> >>
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
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

```
 0
bob.NGs1 (1661)
10/8/2006 5:42:57 PM

Similar Artilces:

POP3 problem #2
I am Using Exchange 2003 on windows 2003 server, after completion and configuration i try to start pop3 service but didnot get any success Any errors reporting in the Application event log? Can you telnet to port 110? What happens when you try? William Mazhar wrote: > I am Using Exchange 2003 on windows 2003 server, after completion and configuration i try to start pop3 service but didnot get any success Did you start from exchange console ? Try it from Services in windows. Malinda Perera. "Mazhar" <Mazhar@discussions.microsoft.com> wrote in message news:575C2A34-D1D...

Quickbooks RMS integration problem
I am trying to figure out how to integrate Quickbooks 2005 Premier with RMS 1.2. I have 2 PCs, a POS/Server up front and a backoffice PC with Quickbooks. In Manager on the backoffice PC I have selected File, Configuration, Accounting Tab, selected Quickbooks 2003, Retrieve and it says it has successfully connected with Quickbooks. However, when I try to set up the GL Accounts by going to Journal, GL Account Setup I get the account mapping table but I am unable to select any accounts (all cells are empty and I cannot manually pick the accounts from a list, there is nothing there). What am I mis...

Problem with OutputTo in Access 2007 + Visual Studio 2005
Please forgive me if I'm posting to the wrong group. I'm converting an application I wrote in Visual Studio 2002 + MS Access 2002. It was a console application that was executed from an Access Macro. The application would open up the current Access Database, it would export a particular report to HTML, then it would call a webservice on a remote server to send out the exported file via email to a distribution list. I'm converting this to Visual Studio 2005 + MS Access 2007, so that I can export the reports to PDF. Everything converted over just fine, except that I can no longe...

Publisher Mail Merge Stapling Printer Problems
Problem: When printing a mail merged document and collating / stapling it, it puts 2 merged documents together rather than printing / folding 1 and then the next record. In Publisher 2003 / 2002 we were able to use KB article #891904 to put a registry value in to solve the problem. In Publisher 2007 however, there is not a fix posted and the previous fix does not work - at least not one that I know of. I have tried all of the usual searches, GOOGLE, MSFT KB, etc. but the articles that do acknowledge 2007 is on the market do not have a fix for it yet. Even KB891904 does not have ...

Problem with OWA Calendar
Calendar of OWA do not show correct time of meetings on Notebook. If use OWA on another PC, Calendar show correct time. Time zone is OK. The Calendar in OWA renders based on the time settings on the PC. It doesn't use anything from the server. Are the times off by 1 hour? If so, even though the time zone may be ok, you should check and see if the "automatically adjust for daylight savings time" is checked. If it is, and you are in a location that does not observe DST, then that would be the culprit. Vice verse - if it is not checked and you do observe DST, it is ...

I've been using Money 2003 small business. When I put in Money 2004 Standard it converted my files to 2004. I want the full small business functionality but now cannot open my files in 2003. The error reads: "Money cannot locate '(file name)' or cannot open it, possibly because it is a read-only file or you do not have permission to change it or your disk drive is write protected." I know the file is not read-only and is not open in another program. I also have the permission to change files on my computer. Does anyone have any suggestions for what to do? I'm prett...

Problem with Asset Allocator pie charts
Everything "seems" to be working fine with Money 2005 Premium (after restarting the program multiple times). I'm having a problem with many of the pie charts. They do not show up at all. I upgraded from Money 2004. Any ideas? I'm running Windows XP Professional if that's of any help. Thanks, Patrick I've seen them going on a new file on Win XP Pro - do you get them on a new file, or do they not appear on both a new and old one? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;...

Problem with POP
I have a Purchase order in change order, this PO must to closed because; y generated a receiving for that order then i posted that transactions. But, with PO don´t change to close, how to do ?????----. If you're asking why the PO didn't change to a closed status after the receipt was processed; once the PO has been fully received and invoiced then the status will change to closed. If you're not expecting an invoice to match to the receipt then you can use the Edit PO window to change the PO status to closed. Doing this should also reverse the GL accrual entry made aft...

Combo Box problem 08-04-07
Why is my combo box in both the table and the bound form not accepting the hand-typed value and produces a "value not an item in the list" error even though I know for sure the value IS in the list? When I select the value from the list it wqorks fine and goes on to the next field.... Thanks Hi, Dennis. > Why is my combo box in both the table and the bound form not accepting the > hand-typed value and produces a "value not an item in the list" error even > though I know for sure the value IS in the list? No, the value _isn't_ in the list, because you ...

mail problems after synch with pocket pc
This problem happened with an autoiovox ppc and now my ipaq ppc too. Using outlook 2003 and the 2003 version of ppc; windows 98. I'm trying to use the same email account on the ipaq as well as in outlook, so I can retrieve email outside of the office on my ipaq. Both work fine independently. But after a synch, the outlook 2003 email will not work until I take the ipaq out of the cradle and both reset the email info in outlook, and erase the email inbox settings on the ipaq. HP suggested that I reset the ipaq before I place the unit in the cradle for a synch, each time. T...

Problems with reports on Money 2004
I am having problems getting onto the reports page as everytime it crashes. I have tried to repair using the disk and uninstalled and re-installed and it still doesn't work. I am told that there isn't a new UK version available so I cannot upgrade. Can anybody help In microsoft.public.money, Jayne Morris <Jayne Morris@discussions.microsoft.com> wrote: >I am having problems getting onto the reports page as everytime it crashes. I >have tried to repair using the disk and uninstalled and re-installed and it >still doesn't work. I am told that there isn't a ne...

Exchange doesnt start at boot up problem.
Hi well here we go today for some reason exchange stopped and i mean totally we found out that asp and iis had totally disappeared?. So we re-install them and then re ran exchange with the disaster recovery switch. And everything seemed fine but how wrong i am. Ok so here we go:- Around 50 % of the users get an error message after send and recive 0x80040111 they recieve and send emails but the error appears these users are unable to send email through owa either. All other users are unaffected except for mac users using entourage they are unable to do anything at all (pop3). Also GFI stop...

problems sending email with crm 1.2

Problem Inserting Images
Whenever I use Word or Powerpoint 2007, and I insert either a jpg or png, I get a line around the top and left side of my images. Like a border. This line sometimes prints, but it shows up online. It mostly happens after I will save the file or move throughout the document. I have no borders, no bevelled edges etc. I can't understand why this keeps happening? are you inserting the pictures into a placeholder on the slide? (where it says "click to add text.") If there's an empty one on the slide, the picture will insert into it automatically. As a test, inse...

Update Problems
I have encountered repeated problems when I try to update my accounts online. Initially, the updates failed about a week ago. I have since "stopped using online services" and then re-started the online services (re-entering my information for each account) in the "Manage Online Services" window. When I do that, I have only been able to successfully update the accounts in Money that initial time. Every subsequent update fails. To make this even more strange, I noticed that my accounts in the Money.msn page update when I click "Update Now" in Money, but ...

Problems shelling another application
I am not sure what is going on with this so I am not sure what to ask. I'll just share my experience and maybe somebody can point out what I am doing wrong. I am attempting to launch another application from my VB app. The code to launch the other app is just a simple shell call (lngProcID = Shell(strAppPath)). The call to Shell does return a value but the app does not appear to have been started (does not show up visually and is not in task manager). Using the value held in strAppPath in the Run dialog (Start->Run) starts the app just fine. I figure this has to be some ...

Equation editor problem.
I have a document created under powerpoint X with equations created by the equation editor. If I open this file with powerpoint 2004, the equations appear as black boxes. However, I can create new equations that look ok. Can anyone suggest a solution to this problem? Thanks. - Mark Are they also black boxes when you run the slide show? If you double-click one of the black boxes, what happens? -- Bob Mathews Director of Training Design Science, Inc. bobm at dessci.com http://www.dessci.com/free.asp?free=news FREE fully-functional 30-day evaluation of MathType MathType, MathFlow, Ma...

exchange store backup problem
I have a job slotted for every night. It does the following 1. ntbackup server state to a backup file A on harddriv 2. ntbackup exchange store to a backup file B on harddriv 3. ntbackup entire server (excluding server state and exchange store) to 8mm mammouth driv This works great most of the time. My problem is, about once a week when it gets mid way through step 2 the server will just shutdown. It almost feels like a hardware problem but that it does not do it all the time. In fact it did it this last weekend and that was the first time it had done it in about 3-4 weeks.. but ...

problem opening word 07
When I open word, a dialog box comes up which contains. among other things, the following instructions which I follow up to step 4 then I hit a snag as noted below. >The document contains macros. Macro language support for this application is disabled. Features requiring VBA are not available, Would you like to open this document read-only? Hide Help Open in Help Window • Microsoft Office Access, Microsoft Office Excel, Microsoft Office PowerPoint, or Microsoft Office Word -¬To check your macro security settings, click the Microsoft Office Button, click <program nam...

Money 2000 Windows XP SP2 Password problems
Hi. * I have reformatted my hard drive (On a Compaq Table TC1000) & reinstalled Windows XP Pro Tablet. * I have then installed the Windows XP SP2. I then restored my backups, which included my mny data file from Money 2000. * I then (amongst other software installs) installed Money from the original CD. * When I tried to start Money, it asks for the password as usual BUT it doesn't accept the usual password. * I have checked that it is the right file, checked Caps Lock, typed carefully several times - to no avail. * Are you aware of any problems with Money 2000 & SP2? I...

Error message Microsoft Outlook has encountered a problem and needs to close
I am unable to open Outlook - get the error message above EACH time I try to launch it. I am running Windows XP, have Norton installed, checked the firewall allows Outlook. I have uninstalled, reinstalled Outlook and downloaded and installed the service pack update. I followed some suggestions on this discussion site as suggested by Nikkin in the response to: "John Frustrated" <fortlaud@mail.com> Sent: 5/1/2004 8:36:21 AM. but nothing works. Can anyone help? What suggestions have you tried? I don't recall the message you are referring to. Rename outcmd.dat an...

Problem copy/pasting images from IE8 to MS Office 2007
I've got a weird problem that just started happening last week on one of my PCs (running Vista Home Premium). If I copy an image from IE8, and paste it into MS Office (Word, PPT, Outlook) the first one works fine, but then when I paste subsequent images, I get a copy of the first one. I'm not sure how to clear the clipboard, but I don't think that's even the problem. It gets more weird... When I paste the second time (or any subsequent copy/pastes after the first), it knows the size of whatever item I copied to the clipboard, but it pastes the first image distorted...

Troubleshooting a non-delivery Problem
I am trying to troubleshoot an intermittant non-delivery problem to a public folder. I have a public foler that is set not to be replciated to the other exchnage servers in our exchange environment. End users create Outlook forms based messages that get sent to the public folder involved. All of the user accounts that use the forms involved are on the save server. The public folder is located on a differnt exchange server. On occassion, when submitting a message using a specific form, the user will get a delayed deliver message that is always followed a couple of days later by a non-deliver...

RMS User Question/Problem
Hello, I Downloaded and installed Microsoft Dynamics RMS, but when I first run it (for the first time) it asks me user ID and password, but have not yet established one, and is the first time I run the program! (Or is it comes with some standard user / default?) User ID: 1 Password: password Robert Armstrong RMS Systems Inc. "F.Misle" <F.Misle@discussions.microsoft.com> wrote in message news:B29A28E5-315C-4A1A-9490-A497651736A0@microsoft.com... > Hello, > I Downloaded and installed Microsoft Dynamics RMS, but when I first run it > (for the first...

Problem connecting to outlook 2007
Hi, my boss can't access his outlook 2007 when he comes in to the office with his laptop. He can only connect if he has his wireless or the vpn on. He gets this error message ox80042108 when he tries to open outlook without wireless or vpn. Thank you for any help or suggestions offered. What type of mail account is it set up as? "k" <k@discussions.microsoft.com> wrote in message news:CA28F0E9-5505-4A12-8833-D2411219A6C8@microsoft.com... > Hi, my boss can't access his outlook 2007 when he comes in to the office with > his laptop. He can only co...