Weird Averaging Puzzle

[Watson]

I have two adjacent columns: cells in the first column
contain numbers, cells in the second column may contain
either the letter "D" or the letter "H". (The second
column never contains any numbers--only one of those
characters.) I'm using the following formula to calculate
the average of the numbers in the first column (column
G). The COUNTIF function excludes zero values from the
average:

=SUM(G6:G175)/COUNTIF(G6:G175,"<>0")

Now, the trick is this: I need to further refine the
average to exclude from the calculation those cells in the
column which are adjacent to cells in the other column
containing "D". I've tried using OFFSET to no avail.
Does anyone have any ideas?

 

[Biff]

Hi!

Numbers in col A and letters in col B:

=AVERAGE(IF(B6:B175="D",A6:A175))

Entered as an array: CTRL,SHIFT,ENTER

Biff

 

[RagDyer]

Try this:

=SUMIF(H6:H175,"<>D",G6:G175)/SUMPRODUCT((H6:H175<>"D")*(G6:G175<>0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I have two adjacent columns: cells in the first column
contain numbers, cells in the second column may contain
either the letter "D" or the letter "H". (The second
column never contains any numbers--only one of those
characters.) I'm using the following formula to calculate
the average of the numbers in the first column (column
G). The COUNTIF function excludes zero values from the
average:

=SUM(G6:G175)/COUNTIF(G6:G175,"<>0")

Now, the trick is this: I need to further refine the
average to exclude from the calculation those cells in the
column which are adjacent to cells in the other column
containing "D". I've tried using OFFSET to no avail.
Does anyone have any ideas?

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF((G1:G175<>0)*(H1:H175<>"D"),G1:G175,""))

 

[Biff]

Ooops!

I see you wanted to *EXCLUDE* "D", no problem:

=AVERAGE(IF(B6:B175<>"D",A6:A175))

Entered as an array: CTRL,SHIFT,ENTER

Biff

 

[JE McGimpsey]

Note that this *includes* rather than excludes cells adjacent to a cell
containing "D", and that it doesn't keep the exclusion of 0 values.