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","")))))))
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","")))))))