Lookup ‘close’ to next row (any)

Topic

New Reply

Hi All,

I use a spreadsheet to keep track of student grades. The last 2 columns have
– the term average, based on averaging tests, etc.
– the letter grade the average converts to, based on a lookup table (eg, first row has 0 and F, second row has 70 and C, etc).

What I would like to be able to do is visually see if the average is “close” to the next row. For example, if a person’s average is 69.5, I’d like to know that since I’d probably give the person the benefit of the doubt and pass them with a C. I’d like to be able to define “close” in some cell (set it to 0.5 or 1, for example).

Right now, the cells for the average and letter grade are filled as blue to set them off from surrounding cells. They are conditionally formatted to pink if the average and grade is failing.

I was thinking of having the average and grade cells add a pattern to the cell – maybe a pattern of dots over the fill color. I think I need 3 conditions:
1st: if passing and close, add the dots (to the blue)
2nd: if failing and close, fill with pink and dots
3rd: if failing, fill with pink

So if the person is passing but not close, the cell is just blue.

I’m not sure how to write the formula for the conditional formats to determine “close”. Any ideas?

TIA

Fred

Reply | Quote

Replies

Let’s say that A50 is the cell in the lookup table containing 70, and that A60 is the cell containing the margin (0.5 or 1 or …)

Let’s say that the cells containing the averages and grades are G2:H40.
Select G2:H40. G2 should be the active cell.
In Format | Cells…, set the pattern for the cells to blue (as you have already done.
Select Format | Conditional Formatting…

Select Formula Is from the dropdown list.
Enter the following formula:

=$G2>
Select Formula Is from the dropdown list.
Enter the following formula:

=$G2>
Select Formula Is from the dropdown list.
Enter the following formula:

=$G2<=$A$50+$A$60

Click Format…
Activate the Pattern tab.
Specify Blue as color, with dots as additional pattern.
Click OK.

Click OK to close the Conditional Formatting dialog.

Reply | Quote

Hans,

Thanks for the quick response. But I’m not sure if I gave enough info.

The lookup table has 6 rows. Something like:
0 F
70 C
77 C+
80 B
87 B+
90 A

If I use your approach, I’m always referring to a single cell or the next one in the table. But when I get a letter grade, I don’t know which row matched, nor do I care. So what I want is that if the grade is within the tolerance of 1 point or whatever is in the “close” range to the next grade up, to shade with dots. If close is one point, then a 69 should be dotted, a 76 should be dotted, and 89 should be dotted, etc. I want to be able to tell based on the letter grade being, say C and the number grade being 72 that the student is not close to a C+ but the letter being C and the number grade being 76.5 that the student is close to C+. This way, I don’t have to go looking somewhere else in the spreadsheet to see if they’re close or not. I’m also not counting on my memory to tell me this.

Fred.

Reply | Quote

That’s different from what you originally asked – you stated that you wanted to see if a student was near to failing/passing.

With an auxiliary column (which can be hidden), you can accomplish what you want. See attached workbook.

Reply | Quote

Hi Hans,

Thanks for the workbook. I took a quick look at and it seems to do what I need.

I say seems because certainly the grades that are “just” lower than the boundary are “dotted” to show they’re in the next HIGHER category. So a 69 or a 79 does show dots to indicate they’re close to 70 and 80 respectively.

However, the grades “just” above the boundary are also “dotted”. I was thinking about whether that was necessary. On the one hand, I’m not going to lower someone’s grade to the next lower letter based on “benefit of doubt” like I would in increasing someone’s grade. On the other hand, it might be useful to know if they are close to the boundary such that if they get low enough marks on the next quizzes and test that they might fall into the lower grade letter; kind of an early warning to keep vigilant in their studies.

I have to think on whether I want dots on both sides of the boundaries. I think if I opt for dots just below the boundaries that the fix would be to get rid of the ABS in the Nearness column and change the formulas in the Conditional Formats to test the nearness value to be between 0 and negative the margin (or just change the margin value to a neg number).

I also see in looking at your conditional formats that the order of the conditions doesn’t seem to matter. I was thinking they would because if the grade was, say, 82, then I wouldn’t want to change the underlying color. I was thinking that a 79, for example, would just add the dots to the fill color based on Format. It looks like you add both the color and the dots (with the color being the same as the Format color) if the grade is passing AND close to the border of the next grade level. I guess you can’t just add the dots to the existing fill from the Format (I tried it and that seems to be the way it works).

As far as my original request, as I said in my 2nd email, I probably didn’t provide enough info. What I said originally was:
>What I would like to be able to do is visually see if the average is “close” to the next row. **For example,** ….

and then proceeded to give just one example of failing. I also mentioned that the letter was based on a lookup table.

Thanks for the help.

Fred

Reply | Quote

Fred

Here is a version with conditional formatting that highlights only grades just below the threshold of a higher grade.

Ken

Reply | Quote

Hi Ken,

Thanks. I still haven’t decided to go with just the under or the over+under as in Hans’ early answer. It’s the end of the semester, so I’m not implementing it for this semester. I have until Sept to decide what to do.

Just one question/observation: In condition 3, you have an AND but only one test. I’m assuming you did this just as a force of habit or for consistency with the first 2 conditions.

Fred

Reply | Quote

Given your intention, wouldn’t

=LOOKUP(MIN(A2+$F$1,100),{0;70;77;80;87;90},{“F”;”C”;”C+”;”B”;”B+”;”A”})

where F1 a small amount for encouragement like 1 and A2 a score, suffice?

Reply | Quote

I was thinking along those lines originally but wasn’t sure. That would have to be a condition in the Conditional Format, since I want the real grade to show in the spreadsheet but with the extra formatting if close. I’m not quite sure how using that would satisfy or not satisfy a CF. Recall also that I’m already using a CF to change the fill of the cell to pink from the normal fill of blue if they’re failing.

Fred

Reply | Quote

Fred,

Just to make sure:

A2: 50, modified 50+$F$1=51

A3: 86, modified 86+$F$1=87

A4: 77, modified 77+$F$1=78

Could you specify what colors would apply to A2:A4, specified per cell?

Aladin

Reply | Quote

Aladin,

Just to be sure even tho it doesn’t matter:
– I enter the grades for tests and quizzes.
– Then there’s a formula that computes the term average based on these. There’s also a formula that does a lookup in a table, per the earlier posting, and derives a letter grade based on the term average.

The former set of cells are clear (no fill) and I’m NOT concerned with these here.

It is the latter cells that I’m concerned with. These are originally blue before I enter any grades with no value (the formula equates to blank or 0, don’t remember, and the formula for the letter grade checks the term average in an IF so the lookup is only done if there’s some positive number in the term average). The blue is a result of Format Fill.

Now suppose I enter a test grade of 50 and say that A2 is where the term average is. Let’s just say that’s 50 also; it’s not important how I compute the term average for this discussion. A2, as a result of CF, turns pink to indicate failing (<70). The 50 is NOT within 1 point of passing (the next row in the lookup table), so nothing more is done. But if the next test brought the student up to a term average of 69, then I'd want A2 and the letter grade to be pink with dots.

A3 computes to 86 based on the test. So right now, that stays blue. I'd like it be blue with dots to indicate it's within 1 point (let's assume I choose "close" to mean 1) of the next letter – 87 and B+ in this case. So 86 is B, but if I give the student the benefit of the doubt (e.g., no absences, participates in class) I'd change it to B+.

A4 is 77 per your scenario. The way Hans did the sheet, that would be blue with dots. That's what I said in my last reply to him that I'm not sure if I wanted to do. My "minimum" approach is to have dots if the term average is "close" BUT LESS THAN the next grade up. But as I also said, I can see value in having the dots for any term average that's within -close to +close of the boundary. So using this additional approach, 77 would be blue with dots, as would be 78 (for C+).

79 would be blue with dots because it is within 1 point of 80 for B, not because it's within 1 point of 77. 80 and 81 would also be blue with dots adopting Hans' additional approach.

83 up to 85 would just be blue; they're not within 1 point of any lower or higher grade.

HTH

If it's still not clear, I'll see if I can get permission to post the file (not mine).

Fred

Reply | Quote

Not sure I’ve got the story right, but try:

1) Activate Insert|Name|Define.
2) Enter GTable as name in the Names in Workbook box.
3) Enter the following formula in the Refers to box:

={0,”F”;70,”C”;77,”C+”;80,”B”;87,”B+”;90,”A”}

Click OK.

A) Select A2:A10 (adjust to suit), the range housing numeric scores.
Activate Format|Conditional Formatting.
C) Choose Formula Is for Condition 1.
D) Invoke the following formula:

=($A2″”)*(LOOKUP(MIN($A2+$F$1,100),GTable)LOOKUP($A2,GTable))

E) Activate the Format button and apply apropriate formatting (pink and dots)
F) Click OK, OK.

BTW: Since we named the relevant table, we can change the original formula:

=LOOKUP(MIN(A2+$F$1,100),{0;70;77;80;87;90},{“F”;”C”;”C+”;”B”;”B+”;”A”})

To:

=LOOKUP(MIN(A2+$F$1,100),GTable)

Reply | Quote

Aladin,

Thanks. I see how the formula works but don’t see how to apply it to the 3 cases (or 4 cases depending on how you count).

Passing and not close to any boundary: – cells with term average and letter grade are blue with no dots (so any and all conditional formats evaluations must fail); don’t worry about the 2 cells, I’d just repeat the test(s) for each one. This is what I think of as the normal case.
Failure case: – cells with term average and letter grade are pink with no dots – failing and NOT within “close”
Failure case but close: – cells with term average and letter grade are pink with dots – failing but within “close” to next higher boundary
Passing but close to next higher boundary: – cells with term average and letter grade are blue with dots – passing but within “close” to next higher boundary

I see how your formula would let me know if the term average is close to the next higher boundary but I don’t see how it would distinguish between failing and close (pink with dots) versus passing and close (blue with dots). I think I need a second test for this, since there’s a 2nd cond format. I know I can order the tests. I think I also need a 3rd cond format.

What this doesn’t cover is if I want dots for being within “close” above or below. That’s ok for now since I may only want close from below (69 gets dotted, 79 gets dotted but 71 and 81 do not get dotted). Per Hans’ approach, I do see how to get dots for being close below and/or above. Not sure I see how to do that with your approach. I do like your approach in that it does not require an extra column but that can always be hidden.

Fred

Reply | Quote

Aladin,

In your step D with the formula

=($A2″”)*(LOOKUP(MIN($A2+$F$1,100),GTable)LOOKUP($A2,GTable))

I understand what is going on except for one thing: why is the MIN needed? It looks like it guards against the possibility of the grade in A2 being >99 with Close (F1) set as 1. If A299, then 100 is used in the lookup. In the case that the grade is exactly 99, with Close=1, it doesn’t matter. But the way the GTable is constructed (or using the arrays), they both exceed the last row of the table anyway.

Thanks.

Fred

Reply | Quote

You’re right. You can reduce the formula to:

=($A2″”)*(LOOKUP($A2+$F$1,GTable)LOOKUP($A2,GTable))

Reply | Quote