conditional average formula

[Jupiler]

Helo, everybody. Every day I have to calculate the workload of employees.
My spreadsheet looks like

mo thue wedn thurs fri
Pete 6 8 9 5 h
Steve h h h 6 8
Nelly s 5 9 5 7

"h" represents a holiday and "s" is absence for medical reason.

Im looking for a (matrix)formula which gives me the average/day, so I can attribute a value to an day of absence. In fact the counted values should look like :

mo thue wedn thurs fri
Pete 6 8 9 5 7.5
Steve 6 6.5 9 6 8
Nelly 6 5 9 5 7


Thanks in advance !

 

[xld]

Can you explain those derived values, it is not obvious to me.

 

[Andrew Poulsom]

If your data is in the range A1:F4, in G2 enter:

=IF(ISTEXT(B2),AVERAGE(B$2:B$4),B2)

and copy down and across.

 

[Jupiler]

Mr. Poulsom. Your solution wil work. Now I still have to get rid of "DIV/0" error.

Thanks to all for your reply!