Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average Formula Problem (Excel 2003)

    Good day to all.

    I have attached a sample that illustrates my problem. Basically I have a column (A) that contains numbers (including the possibility of zeros), some text and spaces. Next to it I have a column ([img]/forums/images/smilies/cool.gif[/img] of names. What I want to do is compute an average of the numbers in column A according to the names in column B. The names in my sample are sorted, but in the real file they are not they cannot be sorted.

    I have tried to create an array formula that does three things: (i) test for numbers in column A, (ii) exclude zero values to get a true average, and (iii) test for a specific name in column B. This seems pretty straightforward to me, but the attached file shows the problems I'm having. I get different results when I try different variations of the array formula and I don't understand why.

    As always I will be grateful for any assistance. This forum has helped so many times that I can't really express my appreciation properly.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average Formula Problem (Excel 2003)

    I would create an intermediate column; you can hide it if you want. See the attached version.

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average Formula Problem (Excel 2003)

    Try array entered,

    =AVERAGE(IF(B3:B96="george",A3:A96))

    =AVERAGE(IF((B3:B96="george")*ISNUMBER(A3:A96),A3: A96))

    I don't see 0 values only blanks

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average Formula Problem (Excel 2003)

    Your second formula worked for me and I thank you very much. The data I sent was just a subset of the original data and I neglected to include the zero values, but I added that condition to the formula you sent and it worked. Once I saw your logic I was able to figure the problem out. I feel like I had tried every possible array configuration but that one. It just didn't occur to me to set all the conditions within the IF.

    Thank you again for your insight.

    Regards,

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average Formula Problem (Excel 2003)

    Hi Hans,

    Thanks for your reply. I had been trying to create a comprehensive formula that would work but when I saw your solution I thought that maybe I should just go with that, as it worked. However, my procrastination in checking back here had a benefit for me, in that Maxflia came up with a formula that works for me.

    I do very much appreciate your efforts and the efforts of everyone on this board. You all do a world of good and you provide the court of last resort, so to speak, when there is no one else to take on complex problems. I have been saved on multiple occasions by the experience, insight and willingness to share of those on this board.

    Best regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •