MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
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 🙂