I am using a result database with grades A+, A, B+, B, C+ C etc. Now A+=10, A=9, B+=8 and so on. I have to calculate the SGPA with a formula using the numerals and not the text, but the sheet should show the text A+, B+ etc. When calculating the formula, the numericals should be taken. The numerals should be in the background denoting the text cell. Please help me in this regard.

You can play tricks, and I'll show you how it works. I forget my VB, but suppose there is a function called mid$(A$,start,number) that extracts from a string staring at start for numer of characters.

If I said Grade$=("A+A A-", Grade*2+1,2)

I could extract A+,A , or A- from the values 0 through 3

You can convert numbers to text and concaenate text so you could display "9.9 A+" in a cell.

You just have to work out the math.

I don't understand the "in the background" part.

You can do the reverse by adding a non-used character to your string "A+*A *A-*"
You can then search for A+, A(space) or A- and get the number.

You can pad with an If statement and remember to create something when things can't be found.

I think I understand what you're getting at. I believe the answer is a simple array lookup table. Here's my final formula:


=LOOKUP(ROUND(B11/9,0),Grades,Final)
or
=LOOKUP(ROUND(SUM(B2:B10)/9,0),Grades,Final)

Look in the sheet attached for a full example and explanation. Here is the long text for everyone.

1) First you need some basic grades to add up, those are given in a column below each child's name
2) You need to add the grades, average them, round the number normally, then convert the number to a letter grade
3) A grid exists on the page somewhere telling the computer how to convert. (see A20:B29)
4) This grid is called an array, it is highlighted as a whole then given a "name" using the INSERT > NAME > DEFINE function so it can be referred to in the final formula as simply "grades"
5) The final grades themselves are also given a name (B20:B29) is named "final"

Now the formula above has enough information to figure out the number grade on its own, then uses the array to find the closest number in the first column, then returns the "final" grade as a letter from the second column.

Works well enough. In the uploaded example, column F shows the best layout, F11 shows the simple letter grade Final Grade.

BTW, if your student gradebook is setup in rows instead of columns, the same concept will work. Just edit the SUM range to be in the proper direction, using the numbered grades properly.

Also, you will need to adjust the number of graded points to the proper divisor. I used /9 for the nine entries in my sample. Yours might be /20 or /30 if there were 20 or 30 graded events that semester.

It's up to you to decide on a student by student basis if the blanks should be counted or not. If not, just manually adjust their divisor down to skip the ones you want to give them a pass on.