Array Formula Returns Incorrect Average

[Guest]

The following array formula returns an incorrect result because it does not ignore empty cells but includes them in the count of cells used to determine the average

=AVERAGE(IF('Completed Action Items'!$F$3:$F$5000="Basler",'Completed Action Items'!$D$3:$D$5000)

The formula returns a result that is lower than the correct answer any time a person has completed tasks that were not overdue, i.e. the cell in that range for any record that was completed on time is blank

Does anyone have a solution for this

As a secondary question, if an individual has completed all of their actions on time, the above array formula returns "#DIV/0!" since the averaging would be dividing by zero. Does anyone know a way to make the array formula display nothing in the cell if there are no number of delinquent days to average

I appreciate any help. Please email (but be sure to fix the displayed email address first).

 

[Peo Sjoblom]

Try

=AVERAGE(IF(('Completed Action Items'!$F$3:$F$5000="Basler")*('Completed
Action Items'!$D$3:$D$5000<>""),'Completed Action Items'!$D$3:$D$5000))

beware of the line wrapping

--

Regards,

Peo Sjoblom

The following array formula returns an incorrect result because it does

not ignore empty cells but includes them in the count of cells used to
determine the average:

=AVERAGE(IF('Completed Action Items'!$F$3:$F$5000="Basler",'Completed Action Items'!$D$3:$D$5000))

The formula returns a result that is lower than the correct answer any

time a person has completed tasks that were not overdue, i.e. the cell in
that range for any record that was completed on time is blank.

Does anyone have a solution for this?

As a secondary question, if an individual has completed all of their

actions on time, the above array formula returns "#DIV/0!" since the
averaging would be dividing by zero. Does anyone know a way to make the
array formula display nothing in the cell if there are no number of
delinquent days to average?

I appreciate any help. Please email (but be sure to fix the displayed

email address first).