I have a worksheet that has 7 columns representing a monthly score.

There are some rows that have no score in column 7

In column 8 i want to display the average of the the last 6 columns with a score, ignoring the blank cell and calculating the average on the 6 cells that have a value.

For example

Row 1 80% 80% 80% 80% 90% 90% 90%
Row 2 80% 80% 80% 80% 90% 90% blank

Row 1 result would be 85%
Row 2 result would be 83.33%

This is easy I know, just not had enough coffee to figure it out.

Assuming Row 1, data starting in column A;
In column 8, =AVERAGE(A1:A7) the function ignores blank cells

As long as the empty cells are blank and not 0s then =average(range) will work.

=average(cells)

this ignores the blank cells

If it’s always just column 7 that may be blank, then it’s pretty easy:

Enter in column 8 (H):

=IF(G1<>””,average(B1:G1),average(A1:F1))

Haven’t checked my syntax on that, but shouldn’t be far off.

EDIT: For the above replies, that’s not what he wants. If you just use the AVERAGE function like that, then you’ll get an average of all 7 values when there are 7 values present, not the last 6.

EDIT 2: Bored enough to check my syntax – it’s correct.

=SUM(A1:F1)/6

A1 is the first cell, F1 is you last cell.

At least, that’s how I’d do it.

=IF(LEN(G1)>0,AVERAGE(B1:G1),AVERAGE(A1:F1))

too slow. +1 what stevomcd says

That’s great guys thanks.

I didn’t want a simple average of all seven cells, just the last 6 with results in. Thanks again.

If you use COUNT to define your denominator then you are averaging only those cells with values in.

=SUM(B3:F3)/COUNT(B3:F3)

If a cell in the range is blank, then it will not COUNT it.
But it must be blank. i.e. not 0 and not “”

If you actually have 0 or “” then let me know as there is another technique.

stoner – that would still give the wrong result when all 7 cells have values.

aha.
well I ‘spose if the structure is consistent then a simple IF function would do it, but it seems so ungainly 🙂