Help displaying a text value for a numerical value

[MrMM]

Hey all,

I'm a teacher trying to create an excel document that analyses student data.

I'm trying to work out what is the average NUMERICAL grade for each student, but only if they have an X next to their name. I used this formula:

=AVERAGEIFS('Pupil Data Table'!J9:J155,'Pupil Data Table'!AA9:AA155,"x")

It works absolutely fine... except that now, I don't want it to give the numerical average as the value in the box, instead I want it to convert to a LETTER grade.

For example, there are 24 possible numbers, and the average could be any one of those numbers. However, if the answer to the average was 1 for example, I don't want it to show 1, but instead show "1C"... If it was 2, I'd want it instead to show "1B"... if it was 24, I'd want it instead to show "8A".

Can anyone help? Hopefully this made sense...

[sourabhg98]

Hello,
If the averages are precisely 1,2,3,4 upto 24 and you want grades as stated, you can use following formula in this particular situation.

Please Login or Register  to view this content.

Else you can also try vlookup method. Check attached for both solutions (check both sheets in the workbook attached).

[sourabhg98]

I am Sorry the formula should be

Please Login or Register  to view this content.

Check updated sheet.excelforum-60.xlsx

[MrMM]

That's brilliant thank you... I've heard of this VLOOKUP thing before...

The only thing is... How do I do all that in one cell? Is it possible to do VLOOKUP in the same cell as

Please Login or Register  to view this content.

... Like, I want the cell to work out the numerical value and then ALSO convert it... all in one cell...

[sourabhg98]

No, Vlookup needs to have a proper data table like I created from where the average value can be searched and corresponding grade values be shown.
However if you can precisely tell what grades you want for what averages we can maybe workout a formula in a single cell without the need to create that data table.

[hemesh]

Hello MrMM, could you post a sample book so things can be worked out much better

[MrMM]

Here,

So I've made it anonymous. So, on the first sheet what you see is names of students and then their grades at different times of the year. Their grades are letter format... 3A... 5A... 6C etc... and then for each grade column, I created a separate column (which I'm hiding), which converts that letter grading in to a chronological number so that I can run a couple of statistics (which you'll see on sheet 2). I wasn't able to find a way to do these statistics on the letter format, so had to change it to numerical.

On Sheet 2,Cell C 47 and 48, I want to know what the average grade is for a group of students. It's easy to do on the numerical values, which I've done... the formula gives me the numerical average... but now I want to convert that BACK to letter format. I.E... It's telling me that the average level for MAT students is 16... which I know is equivalent to 6C... but instead of it writing 16 there, I want it to write 6C...

Does that make sense?

[sourabhg98]

Hi,
Okay then try this formula in C47-

Please Login or Register  to view this content.

I think that would fulfill your requirements.
Do reply if you this was not what you needed.

[JohnTopley]

try

Try

=VLOOKUP(AVERAGEIFS('Pupil Data Table'!$J$9:$J$155,'Pupil Data Table'!$AA$9:$AA$155,"x"),'Pupil Data Table'!$AJ$2:$AK$25,2,1)

and

=VLOOKUP(AVERAGEIFS('Pupil Data Table'!$J$9:$J$155,'Pupil Data Table'!$AB$9:$AB$155,"x"),'Pupil Data Table'!$AJ$2:$AK$25,2,1)


Highlighted formula is range of your Lookup table (numbers in AJ, Codes in AK)

[MrMM]

Awesome, thank you so much, it worked!!! Both worked actually, but I went with the VLOOKUP option because I'm probably going to have to use the data conversion table at some point anyway.

Now I just need to work out how to remove or hide the #DIV/0! error !! I'll work it out!

Thanks so much!

[JohnTopley]

Remove errors ...

=IFERROR(VLOOKUP(AVERAGEIFS('Pupil Data Table'!$J$9:$J$155,'Pupil Data Table'!$AB$9:$AB$155,"x"),'Pupil Data Table'!$AJ$2:$AK$25,2,1),"") Blank if error

or

=IFERROR(VLOOKUP(AVERAGEIFS('Pupil Data Table'!$J$9:$J$155,'Pupil Data Table'!$AB$9:$AB$155,"x"),'Pupil Data Table'!$AJ$2:$AK$25,2,1),0) zero if error


Generally,

=IFERROR(formula, error_Condition)

Error_condition is typically blank/null/zero but can be (for example) a text string such as "ERROR in DATA"