Vlookup function

[Rita Hill]

I am using the Vlookup function to give my students a letter grade.

If the student has a grade of 79.9 and I round it off to 80 - the computer still returns a letter grade of C instead of B. How can I get it to return a letter grade of B when rounded off.

My formula looks like this right now.

=VLOOKUP(I4,$I$21:$J$25,2)

Thanks, Rita

 

[T. Valko]

I am using the Vlookup function to give my students a letter grade.

If the student has a grade of 79.9 and I round it off to 80 - the computer still returns a letter grade of C instead of B. How can I get it to return a letter grade of B when rounded off.

My formula looks like this right now.

=VLOOKUP(I4,$I$21:$J$25,2)

Thanks, Rita

The result you get depends on the values in your table array $I$21:$J$25.

Can you show us what these values are?

 

[Rita Hill]

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Column I</TD><TD class=xl69 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" width=64>Column J</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" height=17>0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #99ccff">F</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" height=17>60</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #99ccff">D</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" height=17>70</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #99ccff">C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" height=17>80</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #99ccff">B</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #99ccff" height=18>90</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff">A</TD></TR></TBODY></TABLE>

This is my grading criteria - but rounding off a 79.6 to 80% still gives the grade of C. I would like for it to return a B

 

[MikeWx]

Will this accomplish what you want?

=VLOOKUP(ROUND(I4,0),$I$21:$J$25,2)

 

[T. Valko]

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Column I</TD><TD class=xl69 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" width=64>Column J</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" height=17>0</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #99ccff">F</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" height=17>60</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #99ccff">D</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" height=17>70</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #99ccff">C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" height=17>80</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: #99ccff">B</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #99ccff" height=18>90</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff">A</TD></TR></TBODY></TABLE>

This is my grading criteria - but rounding off a 79.6 to 80% still gives the grade of C. I would like for it to return a B

How are you rounding? Are you doing it through formatting?

If so, formatting only changes the DISPLAYED value. The DISPLAYED value might be 80 but the true value of the cell will still be 79.6.

Do the rounding in the formula:

=LOOKUP(ROUND(I4,0),$I$21:$J$25)

 

[Rita Hill]

Yes, that does work -- thank you very much!!!