Ignore blank cells

[Teacher]

Hi All,

I have been using this excellent forum to help me set up an assessment spreadsheet (attached)

The idea is that staff enter a fine grade e.g. C1,C2,C3,D1,D2,D3 etc and then for each aspect an average is worked out.

I have managed to undertake this when all cells are filled in however when a cell does not have a grade this average does not seem to work as it counts blank cells for an average so if there are only 2 grades as in row 4 entered a result is not returned.

So is there a way to ignore blank cells when carrying out the formulae in column X,Y and AA.

Many thanks

Olly

[Ron Coderre]

Using your posted workbook, this regular formula returns the Average End Unit tests
Y3: =IF(COUNTIF(AE3:AJ3,"0")=6,"no data",LOOKUP(ROUND(SUMPRODUCT((AE3:AJ3=Code)*Scale) / COUNTIF(AE3:AJ3,"<>0"),0), Scale, Code))

Copy that formula down as far as you need.

Is that something you can work with?

[Richard Buttrey]

Hi,

Averages imply you are working with numeric values which I can't see in the relevant parts of your worksheet. Can you explain exactly what results you expect to see with some examples.

Regards

[Teacher]

Being fine grades each grade could be 1,2 or 3. Say for e.g. C1, C1, C3, C3 I would expect the average to be C2. If you look to the far right you can see a scale assignedto each grade. This thereby allows an "average" to be worked out.

Hope this is clearer. Will try that Ron.

Olly

[Teacher]

Ron it works perfectly many thanks for that

[Teacher]

I am trying to now average the last column "AC" but I cant seem to get this one to work as if there is "no data" or blank comment it returns an error, or calculates this wrong e.g AC4 or 5.

Many thanks in advance.

Olly

[Ron Coderre]

Try changing the formula in AC3 from this:
=IF(COUNTIF(Y3:AB3,"0")=4,"no data",LOOKUP(ROUND(SUMPRODUCT((Y3:AB3=Code)*Scale) / COUNTIF(Y3:AB3,"<>0"),0), Scale, Code))

to this:
=IF(COUNTIF(Y3:AB3,"??")=0,"no data",LOOKUP(ROUND(SUMPRODUCT((Y3:AB3=Code)*Scale) / COUNTIF(Y3:AB3,"<>0"),0), Scale, Code))

Copy the amended formula down.

Does that help?

[snb]

=IF(COUNTBlank(Y3:AB3)=0,"no data",LOOKUP(ROUND(SUMPRODUCT((Y3:AB3=Code)*Scale) / COUNT(Y3:AB3),0), Scale, Code))  

[Teacher]

Ron,

Thanks for you assistance it almost works, if there is a value in any of the 4 columns YtoAB then it returns a value if there is one or more missing then the average is wrong.

SNB I wanted the average if one or two figures were missing that formula returns no data if one or more is missing

Thanks in advance

Olly

[Ron Coderre]

Ooops! My mistake...
AC3: =IF(COUNTIF(Y3:AB3,"??")=0,"no data",LOOKUP(ROUND(
SUMPRODUCT((Y3:AB3=Code)*Scale)/COUNTIF(Y3:AB3,"??"),0), Scale, Code))

Better?

[Teacher]

Ron.

Thanks very much for your time... Just two things:

1) is there any formula to mke sure staff complete this

2) I can formulate most formulas however I am not sure what the ?? and 0 mean in this part you used: COUNTIF(Y3:AB3,"??")=0,"no data",

[Ron Coderre]

Ron.

Thanks very much for your time... Just two things:

1) is there any formula to mke sure staff complete this

2) I can formulate most formulas however I am not sure what the ?? and 0 mean in this part you used: COUNTIF(Y3:AB3,"??")=0,"no data",

In this section:
COUNTIF(Y3:AB3,"??")=0,"no data"

This part: COUNTIF(Y3:AB3,"??")
returns the count of cells containing 2-characters of text ("?" is a wildcard for any single character)
So, in the IF function...if there are no 2-char cells, return: "no data", otherwise calculate the average of the available items.

Does that help?

[Teacher]

Thats great, I do get stuck on the finer details but hopefully will learn little bits.

I am rolling out 7 spreadsheets for staff to use from September down from over 15 currently, this is the last one I am working on and thanks to you its all completed.

[Ron Coderre]

Glad to help!