I would like to add up and average the self scores some of my course attendee’s gave themselves.
A few people were missing on the days that we did this, and I want the formula to ignore columns that are blank. I need to do this over 14 spreadsheets and a couple of hundred people, so do not want to do it manually if I can help it.

At present I have =SUM(B3:AM3)/COLUMNS(B3:AM3).

How can I adjust this to ignore the blank columns?

Thank you in advance, I shall now go and beat myself around the head with a Window’s 98 box…
[url=https://flic.kr/p/nD8SFe]1[/url] by matt_outandabout, on Flickr

Use counta(…) to find the number of non-blank columns.

The AVERAGE function ignores blanks

Thank you irelanst.

I might be tempted to consider the MEDIAN function here. I believe it ignores blanks too, but my main reason would be that it deals better with outliers in your data, e.g. John M gave you a ‘2’ for staff training, but you got better scores from the others.

Being picky, ‘average personal score’ is the sum of scores per person isn’t it?

Oh eh, that’s the exact question I was pondering on the way home…

The commercial answer to this would be to look at various functions like Average, Median, Mode as well as cleaning the data (ie removing the scores which don’t suit you) and then presenting the data in the way which shows you in the best possible light. You then ask for a payrise. Job’s a good ‘un.

You then ask for a pay rise

I love it when a good plan comes together.

This evidence, along with quotes such as “best course in 12 years teaching” and “will influence the rest of my career”, and our finance department phoning me up tell me I a generating “too much surplus – we are a charity…”

8)