Using VLOOKUP and SUM with ROUNDUP ignoring values that are missing or non-criteria text

[Jones90]

Hi,

I'm trying to work out an average grade for children using a VLOOKUP table, also with a ROUNDUP and SUM formula.

Basically, each grade is worth a value. So if there are 3 grades, so 4a, 4b, 4c. This would average to a 4b.
However, if a person misses the 2nd test, so gets a 4a, ABSENT, 4c, how can I calculate the average through ignoring the ABSENT grade.

The average would be 4b, this can be worked out easily manually here, but when there are 10 tests and 1 grade missing, I need a formula to help make it work.

I have attached a spreadsheet to show how it should work. Hopefully there is a smaller formula to use than one I made myself previously which does not work on these missed tests.

Thanks

[Pete_UK]

You failed to mention that you might have "No Level" or "Left", as well as "Abs" in addition to the grades in those cells. What should happen in these cases?

Also, 21 will match first with 06A in your table, rather than the 3A that you show in cell G11.

Pete

[Jones90]

Good point, basically any text or blank cell needs to be ignored. Therefore, unless it is a grade listed in the levels table then it should be disregarded.

This is why my initial formula does not work as it uses a counta function which will include the text as a value when dividing.

It it matches O6a or 3A it does not make a difference in this case.

Hope you can help, thank you

[Pete_UK]

Okay, I put this formula in F2 (based largely on what you already had - changes shown in red):

=ROUND((IFERROR(VLOOKUP(C2,Levels!$A$2:$B$44,2,),0)+IFERROR(VLOOKUP(D2,Levels!$A$2:$B$44,2,),0)+IFERROR(VLOOKUP(E2,Levels!$A$2:$B$44,2,),0))/(COUNTA(C2:E2)-COUNTIF(C2:E2,"ABS")),0)

Basically, each VLOOKUP returns zero if the "level" is not present in your grades table, and the COUNTA term is modified by subtracting the number of ABS in the range. You can easily modify that to take account of any other proscribed text values.

The formula returned 21, as expected.

Hope this helps.

Pete

[maras_mak]

Does this do what you want?
F2 and copy down

Please Login or Register  to view this content.

[Jones90]

Thank you both for your replies.

Your formulas do work in their given section,I can see that I did not explain clearly enough at the start.
Each block/3 grades counts as maybe a term, so the results are across the whole year.

Therefore, the Autumn 1 average would look at the 3 grades to the left. However in Autumn 2, it would look at the 3 to the immediate left as well as the 3 scores from Aut 1.
Spring would average the 3 grades from Spring, Aut 2, and Aut 1. Summer would look at those 3 and the 9 previously.

I hope that makes sense? Apologies for not making it clearer earlier. I have attached a new copy with the headings for clarity.

Thank you once again

[Pete_UK]

To avoid your increasingly long formulae, I put the numbers to one side, with this formula in X1:

=C1

which can be copied across the row, and this one in X2:

=IFERROR(VLOOKUP(C2,Levels!$A$3:$B$44,2,0),"")

which can be copied across and down as required. To bunch all the numbers together, I just deleted the columns which related to your calculations.

Then you can use these formulae in the cells stated:

F2: =ROUND(AVERAGE(X2:Z2),0)

K2: =ROUND(AVERAGE(X2:AC2),0)

P2: =ROUND(AVERAGE(X2:AF2),0)

U2: =ROUND(AVERAGE(X2:AI2),0)

Note that the ranges just increase by 3 columns each time. Copy these formulae down as required - your formula in the adjacent columns will return the appropriate grades. AVERAGE ignores the blanks in the numbers to the right, which occur wherever you have used text which is not in your table of grades, so you don't need to explicitly exclude those, and the formulae become much simpler.

Hope this helps.

Pete

[Jones90]

Thank you for your help. The helper column definitely made it easier and this can just be hidden.

Thanks

[Pete_UK]

Glad you found it helpful - thanks for the rep.

Pete