PDA

View Full Version : average letter grades forumula



BrianMH
02-19-2009, 11:38 AM
Hi,

One of my friends wants a forumula that can convert a range of letter grades into the average of those letter grades.

I have a formula that ignores the blanks and converts the letters to numbers sums and averages them and then converts back to numbers. It works except for the last if statement. Its the same as the previous but the last bit for G won't work. It works if I take this last section out of the formula. I know a lookup would probably work better but I really want it to be a single formula so he can use it on multiple worksheets with out having to add a column to look up from. Below is what works and what doesn't.

Works

=IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=7,"A",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=6,"B",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=5,"C",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=4,"D",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=3,"E",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=2,"F",""))))))

Doesn't





=IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=7,"A",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=6,"B",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=5,"C",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=4,"D",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=3,"E",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=2,"F",IF(ROUND(SUM((C7:H7="A")*7,(C7:H7="B")*6,(C7:H7="C")*5,(C7:H7="D")*4,(C7:H7="E")*3,(C7:H7="F")*2,(C7:H7="G")*1)/COUNTA(C7:H7),0)=1,"G","")))))))

Bob Phillips
02-19-2009, 11:53 AM
How about this array formula?

=CHAR(64+SUM(IF(ISNUMBER(MATCH(C4:I4,{"A","B","C","D","E","F","G"},0)),MATCH(C4:I4,{"A","B","C","D","E","F","G"},0)))/COUNTA(C4:I4))

Bob Phillips
02-19-2009, 11:55 AM
or maybe

=CHAR(64+ROUND(SUM(IF(ISNUMBER(MATCH(C4:I4,{"A","B","C","D","E","F","G"},0)),MATCH(C4:I4,{"A","B","C","D","E","F","G"},0)))/COUNTA(C4:I4),0))

mdmackillop
02-19-2009, 12:03 PM
Hi Brian,
You're a braver man than I, tackling such formulae!

I would go for a UDF such as

Function AvGrade(Data As Range)
Dim cel As Range, tot As Long, Cnt As Long
For Each cel In Data
If cel <> "" Then
tot = tot + Asc(cel)
Cnt = Cnt + 1
End If
Next
AvGrade = Chr(Int(tot / Cnt))
End Function


You may want to adjust the rounding for the best solution.

BrianMH
02-19-2009, 12:06 PM
or maybe

=CHAR(64+ROUND(SUM(IF(ISNUMBER(MATCH(C5:H5,{"A","B","C","D","E","F","G"},0) ),MATCH(C5:H5,{"A","B","C","D","E","F","G"},0)))/COUNTA(C5:H5),0))

Works great except for on the last row. I'm not sure how this formula works but thanks. Any idea why its not working on the last row? I'm going to have to study the formula because its obviously alot simpler than mine.

BrianMH
02-19-2009, 12:14 PM
Hi Brian,
You're a braver man than I, tackling such formulae!

I would go for a UDF such as

Function AvGrade(Data As Range)
Dim cel As Range, tot As Long, Cnt As Long
For Each cel In Data
If cel <> "" Then
tot = tot + Asc(cel)
Cnt = Cnt + 1
End If
Next
AvGrade = Chr(Int(tot / Cnt))
End Function

You may want to adjust the rounding for the best solution.
I like that solution. The only problem is that it returns M for one thing. I am going to study this a bit more. I like problem solving.

edit:
I just realised its because the U should be ungraded. Adding if cel <> "" and cel <> "U" then should solve that and I think I'll change it from
AvGrade = Chr(Int(tot / Cnt)) to AvGrade = Chr(round((tot / Cnt),0))

Thanks.

Bob Phillips
02-19-2009, 01:17 PM
Works great except for on the last row. I'm not sure how this formula works but thanks. Any idea why its not working on the last row? I'm going to have to study the formula because its obviously alot simpler than mine.

Because you are a row out of step. You have the formula on row 4 pointing at cells on row 5, so the last line points at an empty row.