My knowledge of Excel has not served me well...

July 2, 2008 10:07 AM

Within cell C2, If the content of a cell in $A$10:$A$900 is the same as the content in A2, I would like Excel to Average the contents of column H in the rows that match A2...does that even make sense?

Because I have tried explaining this to Excel in as many ways as possible, and all it ever says back is "#value" or "0".

Any help would be REALLY appreciated!

posted by InfinateJane to computers & internet (6 comments total)

If I understand correctly you could do this:

1. Use the SUMIF formula SUMIF($A$10:$A$900,$C$2,$H$10:$H$900) to get the sum of the values from column H.

2, Use the COUNTIF forluma COUNTIF($A$10:$A$900,$C$2) to get the number of occurrences of $C$2 in column A.

3. Divide result from 1 by result from 2.
posted by preparat at 10:21 AM on July 2

Take each component separately. Your first thing sounds like a MATCH, the second thing sounds like a COUNTIF along with a SUMIF to simulate the behavior of AVERAGE.
posted by odinsdream at 10:22 AM on July 2

Sorry, replace $C$2 with $A$2 in the above comment.
posted by preparat at 10:22 AM on July 2

If you're using Excel 2007, you can use AVERAGEIF: =AVERAGEIF(A10:A900, A2, H10:H900)

If you're not using Excel 2007, you can enter an array formula: =AVERAGE(IF(A10:A900=A2,H10:H900))
In order to enter it properly, you have to hold CTRL and SHIFT when you press ENTER. The formula will then appear in brackets: {=AVERAGE(IF(A10:A900=A2,H10:H900))}
posted by stefanie at 10:31 AM on July 2

stefanie - that's great (and elegant), thanks for sharing!
posted by preparat at 10:38 AM on July 2

Stefanie, that worked a treat! Thank you :)
posted by InfinateJane at 1:19 AM on July 3