Array Formula Returns Incorrect Average

  • Thread starter Thread starter Guest
  • Start date Start date
G

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


Steve Collins said:
The following array formula returns an incorrect result because it does
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
email address first).
 
Back
Top