Can I check a calculation result against a list of values in Excel?

Factual Questions
1

I’ve made a spreadsheet for planning 2005 class schedules. I plug in a date, and adjacent cells calculate the day of the week, and the dates of succeeding classes, once per week for X number of weeks.

What I’d like to do is put in a column of dates that represent course “holidays” that we don’t want to hold classes on, like July 4, 2005. If one of those dates gets caluleted in a class date cell, I’d need some kind of alert action, maybe like the font in that cell turns red.

BTW, each cell now simply reads the cell to its left and adds seven – using a date dormat. A1 = ________(date entry); B1=A1+7; C1=B1+7…
There are a couple of other cells thaT I don’t think would be affected- one calculates what day of the week the original date entry is, and another calculates the start and end times based on what day the course is held on (weekday classes have one schedule, weekend classes are different).

2

You can use Conditional Formatting on the cell. You can set it so that if the cell’s value is, for example, between 1 and 10, it should be colored red, otherwise, black. That sort of thing.

3

So if I can parse out the right kind of IF statement, I can have the cell turn color. Cool, that’s half the problem, and I can find the syntax for that I’m sure. That still leaves the parsing of checking a value against a list.

Can anybody shed some light here, even if it’s to tell me it can’t be done?

4

There’s a feature called “data validation” that can check input against a list, but I’m not sure it’ll work on a formula result as opposed to keyboard entry. You might check it out though.

The better solution is to use a HLookup or VLookup function. The two functions are almost identical, just exchanging the roles of rows and columns versus the other function.

Read the help, but the basic idea is to use one cell value as a key to compare with a list of values elsewhere in the sheet. If the value is found, H/VLookup returns another value from the same row/column as the found value. If you provide the right paramters, it’ll return a #NA if the value you’re looking for is not found.

In your case, all you care about is found versus not found. So if you get a #NA, the date is not a holiday and if you get a result, the date is a holiday.

And you can also use conditional formatting to make the NA stand out.

5

On a separate tab in the worksheet, create a 3-column table. In the first column, list the dates that are holidays in chronological order. In the second column, type in the names of those holidays. Copy the first column to the third column.

Now, highlight the data in columns A and B and name that range dates1. Highlight the data in columns B and C and name that range dates2. Now, replace the formulas you have now (i.e. +A2+7) with the following formula:

=+IF(ISNUMBER(A2),+IF(ISERROR(+VLOOKUP(A2+7,dates,2,FALSE)),+A2+7,+VLOOKUP(A2+7,dates,2,FALSE)),+VLOOKUP(A2,dates2,2,FALSE)+7)

What this does is looks at the cell to the left to see if it’s a number(dates are treated like numbers in excel). If it is, then it adds 7 to the number and looks up the result in the range you named dates1. If it finds that date in the range, it will show the name of the corresponding holiday. If it does not, then it shows the date 7 days later than the cell to the left. IF the cell to the left shows the name of the holiday, that is not a number, so it finds the corresponding date of that holiday in the range you named dates2 and adds 7. I tested this and came up with this:

16-Aug 23-Aug 30-Aug Labor Day 13-Sep 20-Sep 27-Sep 4-Oct Columbus Day 18-Oct 25-Oct

Hope this is good. To use conditional formatting, highlight all the dates and select Format, then Conditional Formatting from the menu. Set Condition 1 to read Cell Value Is not between 1 and 10000000, hit the format button and select the color red for the font. This will make the holiday fields show in red and keep the other fields the default color.

Hope this helps. :slight_smile:

6

I’m a great proponent of clever Excel formula tricks, but even this is one I’d do manually.

I mean, there are only 10 federal holidays per year. Just do it by hand.

7

But there are thousands of class dates to check against the list. We haven’t quite finished 2004 classes yet, but between now and Dec 31 we already have more than 800 scheduled. We have 6 classrooms and they are often all in use at once.

Plus we make up additional, non-Federal, holidays when we don’t hold classes. For instance, we run pregnancy classes for couples, and we’ve learned not to schedule classes on Mother’s Day weekend. We don’t do classes on days there are Wisconsin Badger football home games, because we are close to the stadium and parking is impossible. We run special education events on days like “National Women’s Health and Fitness Day” (9/29 this year), and about a dozen other days like it, so we don’t want to schedule regular stuff and fill up our space on those days.

Rufus Xavier, thank you. That looks pretty much just what I want. I have Excel at home, but not the spreadsheet I need to test it on, so I will test at work tomorrow and let you know.

To further describe the spreadsheet, each column represents class #X of classes anywhere from 1-10 weeks long. Each row represents one schedled instance of a classe series. (We repeat a 7-week delivery and infant care class about 50 times a year, and that is one of about 15 different birthing or parenting series we do.) Each of the 15 different classes is in a separate worksheet within the same workbook.

Now that I’m confident it’s possible, assuming your formula works I’ll be working to modify the output as follows. Where your sample output above shows “Labor Day” in the 4th cell, I want it to show 13-Sep in red. That will indicate class 4 is held a week later than expected because of a holiday.

Thanks very much. I’m certainly no expert, but I’m not nearly as helpless in Excel as I am in Access. With a start like this I’m sure that I will have my boss and co-workers go ooh! and aah! in short order. :smiley: Oh yeah, and my job will be made much easier, so I probably owe you a few drinls at least.

8

Even easier! In that case, you only need one lookup column with the dates you want excluded. Name the range “dates”. Then the formula you want is this: =+IF(ISERROR(+VLOOKUP(A2+7,dates,1,FALSE)),+A2+7,+A2+14)

Then the conditional formatting should be Cell Value is greater than =+A2+7, and format the font in red. then copy to all the other calculation cells.

The only problem I forsee with this one is if you ever get two weeks in a row with holidays. But, heck, I can’t solve every problem! That’s your job! :smiley:

Looks like you’ve had a few drinls too many already. :smiley:

9

Ok I admit it, I was interested enough to open my home spreadsheet and try it, and it worked perfectly!! (After a brief screwup until I realized it needed to be pasted into cell b2 to propagate correctly)

Two adjacent holidays do fool it, however. It catches the first date, not the second.

But it they’re NOT adjacent, it catches them both perfectly.

:D:D:D

Whoo hoo!!

10

Thank you again, Rufus Xavier. I looked at our calendar for the next couple of years and there are several instances where we have two “holidays” on successive Mondays or Saturdays, but no instances of 3 in a row. So I modified your formula to basically repeat for one more iteration when it found a holiday. That solved the Christmas New Year issue. :slight_smile:

Here’s how I modified it:
=IF(ISERROR(VLOOKUP(A2+7,dates,1,FALSE)),A2+7, IF(ISERROR(VLOOKUP(A2+14,dates,1,FALSE)),A2+14,A2+21))
Then, for planning purposes I added another row that would flag holiday “hits” in color, but not skip skip forward to the next date. The formula simply imcrements 7 from the cell to the left, but I modified your VLOOKUP code to use as the definition of the conditional formatting:
A5=VLOOKUP(A5,dates,1,FALSE)
So if the cell hits one of the list values it just turns red, and increments normally.

Now our class coordinators can plug in any start date and immediately see two different options for class dates, based on whether or not they choose to cancel sessions of classes on holidays.

Really, I can’t thank you enough for this string of code. I can see several applications for it.

11

Aw shucks. You made me blush. I’m just glad I could help a fellow doper out. :cool: