Average IF Formula

[randolphoralph]

I am trying to use the formula below to return an average. The formula is returning #DIV/O! error instead of the average. I have checked the data in 'Data'!$K$2:$K$57592 and the formula does not divide by zero.

=[FONT=Times New Roman]AVERAGE(IF(('Data'!$E$2:$E$57592=$A4)*('Data'!$E$2:$E$57592=$A5)*('Data'!$E$2:$E$57592=$A6)*('Data'!$E$2:$E$57592=$A7)*('Data'!$E$2:$E$57592=$A8)*('Data'!$E$2:$E$57592=$A9)*('Data'!$E$2:$E$57592=$A10)*('Data'!$E$2:$E$57592=$A11)*('Data'!$E$2:$E$57592=$A12)*('Data'!$E$2:$E$57592=$A13)*('Data'!$E$2:$E$57592=$A14)*('Data'!$E$2:$E$57592=$A15)*('Data'!$I$2:$I$57592=BD$1)*('Data'!$K$2:$K$57592<>"")*('Data'!$A$2:$A$57592="Yes"),'Data'!$K$2:$K$57592))[/FONT]

When I change the formula to not be equal to A4-A15 the formula works and returns the correct average.

=[FONT=Times New Roman]AVERAGE(IF(('Data'!$E$2:$E$57592[COLOR=red]<>[/COLOR]$A4)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A5)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A6)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A7)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A8)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A9)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A10)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A11)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A12)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A13)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A14)*('Data'!$E$2:$E$57592[COLOR=#ff0000]<>[/COLOR]$A15)*('Data'!$I$2:$I$57592=BD$1)*('Data'!$K$2:$K$57592<>"")*('Data'!$A$2:$A$57592="Yes"),'Data'!$K$2:$K$57592))[/FONT]

I am not sure what the issue is. Can anyone provide some insight?

 

[theta]

You would be better off using a range (instead of linking the <> to individual cells)

This array formula (Press Ctrl+Shift+Enter NOT just Enter) will exclude items from a range when summing. Combine this with a count and you can produce an average

=SUM(IF(C1:C6<>A1:A6,D1:D6,0))

The range that contains the ID
The range that contains items to exclude (IDs to exclude)
The range to SUM

Try it out and see if this can be worked. If not SUMPRODUCT comes to mind

 

[theta]

NOT TESTED

You could try this. This again is an array formula

=SUM(IF('Data'!$E$2:$E$57592<>A4:A15,'Data'!$K$2:$K$57592,0))/SUM(IF('Data'!$E$2:$E$57592<>A4:A15,1,0))

 

[randolphoralph]

I tried the following and it still does not provide the average.

=AVERAGE(IF(('Data'!$E$2:$E$57592=$A4:$A15)*('Data'!$I$2:$I$57592=BD$1)*('Data'!$K$2:$K$57592<>"")*('Data'!$A$2:$A$57592="Yes"),'Data'!$K$2:$K$57592))

 

[randolphoralph]

I think that the issue I am having is that $A4-$A15 should be "Or" and not "And" for the IF formula, but I am not sure how to change the formula to reflect that.

 

[randolphoralph]

I have done some additional testing and the problem begins when I am trying to look at more than one cell in Column A.

The following formula worked and returned the correct average.

=AVERAGE(IF(('Data'!$E$2:$E$57592=$A4)*('Data'!$I$2:$I$57592=BD$1)*('Data'!$K$2:$K$57592<>"")*('Data'!$A$2:$A$57592="Yes"),'Data'!$K$2:$K$57592))


When I added the part in red I am getting the #DIV/O! error.

=AVERAGE(IF(('Data'!$E$2:$E$57592=$A4)*('Data'!$E$2:$E$57592=$A5)*('Data'!$I$2:$I$57592=BD$1)*('Data'!$K$2:$K$57592<>"")*('Data'!$A$2:$A$57592="Yes"),'Data'!$K$2:$K$57592))

 

[theta]

Did you try and of my posts?

 

[theta]

Also, try and manually filter to represent the same record.

It may be that there are no items left (to average) after all of the exclusions.