Counting Higher and Foundation grades.

**edmdas** · 04-21-2008, 04:52 AM

Attached is a grade sheet.

Students sit either a Higher (H) or Foundation (F) paper.

The grade boundaries are respectively different, column F & G.

We visually check whether they have taken H or F and then read the respective grade.

I wish to count the number of each grade but avoid double counting. i.e. if a student sat a H paper and shows a ‘C’ in both columns (F & G) I wish to only count the ‘C’ grade in column G and ignore the ‘C’ in column F.

I have tried using the following formula (amongst many others)

Please Login or Register  to view this content.

As an example for counting A grades in the H column each column but it can't get pass the N/A

I am looking to either:

Combine the formulas in column F & G to convert grades dependent on H or F in column B and return a grade

OR

Use separate formulas to count grades in each column F & G that can get passed the NA

Any help would be appreciated; I've hit a mental block on this!

Thanks

**arthurbr** · 04-21-2008, 08:38 AM

Your sumproduct formula returns a NA value because it contains NA values in row 7.
You could introduce a ISNA condition for your LOOKUP formula.

Please Login or Register  to view this content.

and try

Please Login or Register  to view this content.

**ratcat** · 04-21-2008, 09:48 AM

G'day,

I response would have been sooner some idiot (me) click on no instead of yes when save the ss.

Maybe it was reading the starting thread over and over and over.

I think this may solve some of the concerns that's been posted.

I also like to thank arthurbr for the first response code, that helped heaps. Good on ya mate

But did not apply the second susgested code.

Cheers

ratcat

**edmdas** · 04-21-2008, 11:25 AM

Amazing;

Yes my starting thread could have used some clearer language and deleting of the word 'each'. I always try to be aware that what’s clear to me is ‘mud’ to someone new, so a ‘U’ for me on this one I think.

Thank you for your persistence.

Arthurbr your ‘ISNA’ code was new to me. Just when you think you’ve got an idea of most of the formulas up pops a new one you’ve missed. How I have never seen or read IS statements before I don’t know, but this will be very useful for some other sheets I’m running, thank you.

Ratcat, you spoil me for choice, a very elegant solution and I have already finished the sheet and placed the numbers on my Head of Departments desk Your

Please Login or Register  to view this content.

I had tried but rejected due to the NA issue so going back to the original ‘LOOKUP’ formula and using ISNA is a great work round.

Really appreciate your minds time on this guys, sterling effort, A*

THANKS

**ratcat** · 04-21-2008, 10:53 PM

G'day

Thanks for the thanks.

I just want to point out the in the ss I changed the "A*" to "A+". Due to the fact that the "*" is a wildcard. See excel help type in keyword "wildcard"

Eg In the countif formula see "A*" it will count all "A" and "A*" in that range. Therefore you will double on the overview (summary) of the marks.

If there were 5 A and 5 A* answer will be 10 in the A* cell.

Change the "*" to "+" or another symbol answer will be 5.

Cheers

**edmdas** · 04-25-2008, 05:28 AM

Yes I knew about this, I have been using quotation marks which get round this problem although a little irritating to do every time by hand; HOD likes to see A* not A+, I know I know, but what can you do?

Please Login or Register  to view this content.

So thanks again.

Counting Higher and Foundation grades.

LinkBack

Thread Tools

Rate This Thread

Display

Counting Higher and Foundation grades.

Perfect!

Quotation marks

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions