Cell being ignored in Average formula

[Bleveck90]

Hi,

I have a spreadsheet for an employee review. In each section of the review there is a numerical score assigned 1-5. On another sheet these scores are pulled together and then averaged to give a score for the employee. This average formula is ignoring one of the cells in the range, and I don't know why. I believe it has to do with the fact that the cell contains a formula, that assigns a score based on how many days the employee has missed which is entered in another cell rather then just a numerical value, but it is still displaying the value and not the formula on the second sheet, it just won't add it to the average. For example:

Employee A Scores:

Safety: 1
Job Knowledge: 2
Adherence to Quality Standards: 1
Productivity: 2
Time, Attendance & Dependability: 1
Communication Skills: 2
Planning and Organizing: 1
Working Relationships: 2

The score for "Time, Attendance, and Dependability" is a result of the employees missing 5 days, determined by this formula:

=IF(ISBLANK(N39),"",IF(N39>=5,"1",IF(N39>=4,"2",IF(N39>=2,"3",IF(N39>=1,"4",IF(N39>=0,"5",IF))))))

On the second sheet it collects the scores into a table like so.

1
2
1
2
1
2
1
2

And then averages them (=Average A1:A8) which should give an average of 1.5, but instead ignores the value for the attendance and gives 1.57. If I manually enter a 1 in either the cells for attendance (sheet 1 or 2) then the average works.

Thanks in advance for any help you can provide.

[Glenn Kennedy]

Take the " out from around the numbers in your formula. "5" is recognised as being TEXT not a number.

[TMS]

As Glenn has said, you are producing Text values. An alternative and shorter formula is:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Or, simpler logic:

Please Login or Register  to view this content.

And it's even shorter (but aesthetically less pleasing)


Regards, TMS

[Bleveck90]

It works! I figured it was something simple. Thanks.

[TMS]

You're welcome. Thanks for the rep.