Difficulty with IF, ISBLANK Formulas and Conditional Formatting in Excel 2010

dagger26

New Member
Joined
Dec 28, 2011
Messages
3
Hi there,

First, thanks in advance for any help.

I am working on an MS Excel 2010 spreadsheet that tracks attendance records. If students miss two or more days in a row, I need to follow up on it. My goal is to create a status column for each student where the cell will turn red if they miss two or more days in a row so it brings it to my attention.

If students are in class, under that day's column it says "attended." If they did not attend, the cell is blank. So if there are two consecutive empty cells in a student's row I want their status to turn red. For example if student 1 has empty cells in cells b1 and c1, I want their "status" cell to be red.

I have tried doing this using nested IF functions and using ISBLANK but I frankly don't think I am anywhere close to figuring it out.
 
If a student miss Friday and Monday neither my solution nor your will catch the consecutive faults.
Good thinking. :)

Hopefully the OP will clarify the layout. In the meantime, my guess would be that Sat & Sun would not be listed and for my method I would use an extra helper cell (C2 below).

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXY
1MonthCurrent dayCols
2December3022ThuFriMonTueWedThuFriMonTueWedThuFriMonTueWedThuFriMonTueWedThuFri
3SudentsStatus1256789121314151619202122232627282930
4Student1ConsecutiveAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
5Student2OKAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
6Student3OKAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
7Student4ConsecutiveAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
8Student5OKAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
9Student6ConsecutiveAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
10Student7OKAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
11Student8OKAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
12Student9ConsecutiveAttAttAttAttAttAttAttAttAttAttAttAtt
13Student10OKAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAttAtt
Consecutive absences
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B41. / Formula is =B4="Consecutive"Abc
 
Upvote 0
Yes, we need the real lay-out (frequency control sheet) to provide a practical solution.

In the meantime i stand with my formula in #10

M.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top