Project: Use tracking

There’s another type of incremental formula that can be used to track how recently someone did a particular thing, or how recently a particular thing was used. These things could be almost anything, though it works best with finite sets. It could be used to track equipment usage by tracking how recently each item was used, and who used it. It could be what household chores and been done, and who’s been doing them. As an example I’ve made a spreadsheet that tracks how recently a given professor has brought their class in for library instruction. It’s a modification of the one I did as a countif() example.

In the modified example I’ve added, among other things, a column in the ‘Library Instruction’ worksheet which specifies which professor the attending Undergraduate or Graduate class belonged to. So if Librarian #3 taught a library instruction session to Teacher #7’s class on March 5th, the cell in the date column would be 03/05/13, the cell in the instructor column would be “Librarian #3”, and the cell in the professor column would be “Teacher #7”.

Then I added a new worksheet, named “Teacher_MostRecent”. In this worksheet there’s a column for each professor. Their names are in the first row, and the most recent date they brought a class in is in the second row.

Then on the Summary_Tables page I added a table listing all of the professors, and their most recent date. That’s why I recently discussed how to switch rows and columns without breaking formulas – the values are in a row in the Teacher_MostRecent worksheet, but they’re most easily read as a column.

So what’s the formula? As in any case there are many ways to do it, but for this example I restricted it to functions I’d already discussed in this blog. That said, let me give you an example from cell A2 of Teacher_MostRecent:
=MAX(Class_Date*COUNTIF(‘Library Instruction’!$C2,A$1),A3)

Here’s what it breaks down to.
– COUNTIF(‘Library Instruction’!$C2,A$1): Count the number of times the the professor listed on this row of the Library Instruction table (‘Library Instruction’!$C2) is the same as the one listed at the top of this column (A$1). In other words, if they’re the same then the result will be one (1). If they’re different, the result will be zero (0). I can’t use the name of the column here because then it would be counting the number of times that professor brought a class in, not whether this particular row was this professor.

Note also that I have dollar signs in the references, making those parts of them fixed. That is to say, ‘Library Instruction’!$C2 will always be column C even if the formula is pasted into another column, and a$1 will always be the first row even if it’s copied to another row.

– Class_Date*COUNTIF(…): Since dates are stored as numbers, and the countif() part returns either 0 or 1, this will either be Class_Date*0=0, or Class_Date*1=Class_Date.

– MAX(Class_Date*COUNTIF(…),a3): A3 is the cell below A2, and it’s a relative reference so no matter where you paste this formula it will always refer to the cell below the cell with the formula. So if it’s not the right professor for this column this is the maximum between 0 and the value in the cell below which, since we’re only dealing with positive values, will always equal the cell below. If it is the right professor for this column then it’s the maximum between Class_Date and the value from the cell below.

If the classes are sorted in reverse chronological order (most recent first) then Max(Class_Date,cell below) will always be Class Date since higher rows are more recent dates and therefore bigger numbers. However, if the classes are sorted in normal chronological order then the first time there’s a match the result will be equal to Class_Date, and after that it will always equal the cell below because that’s the largest possible date for that professor.

That might be confusing without an illustration, so here’s an example of how it could work, with menu tracking over a two week (14 day) span. Mac & cheese was eaten for dinner on the last night, #14, so it starts at the bottom with 14 and never changes. By contrast tuna casserole was eaten on the third night, and never again, so its column stays 0 until it reaches the row for day 3, whereupon it changes to 3. So you can just look at the top row of values (which I’ve bolded in the table below), rather than having to search the list to find the last time a certain meal appeared in the list.

Records | Most recent
Date Dinner | Stroganoff Mac & Cheese Tuna Casserole Spaghetti Pizza
1 Stroganoff | 11 14 3 13 10
2 Mac & Cheese | 11 14 3 13 10
3 Tuna Casserole | 11 14 3 13 10
4 Spaghetti | 11 14 0 13 10
5 Pizza | 11 14 0 13 10
6 Stroganoff | 11 14 0 13 10
7 Spaghetti | 11 14 0 13 10
8 Mac & Cheese | 11 14 0 13 10
9 Pizza | 11 14 0 13 10
10 Pizza | 11 14 0 13 10
11 Stroganoff | 11 14 0 13 0
12 Mac & Cheese | 0 14 0 13 0
13 Spaghetti | 0 14 0 13 0
14 Mac & Cheese | 0 14 0 0 0