Average Formula Problem (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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,

Reply | Quote