Making a vehicle maintenance log

AVM,

You can use the MAX function within a LOOKUP or MATCH function. To avoid problems with defined cell ranges, use Header Rows and Footer Rows. They are special in that they won't be incuded in references to entire columns.

If you post your table here, we can make specific suggestions.

Jerry

Hi AVM,

For the cell that will warn it's time for an oil change, you need two values: The current Tach reading (which will be the MAX() value in the Tach column), and the Tach reading at the previous oil change. How to extract the second value depends on how each maintenance item is entered in the log. A screen shot would help with determining that.

Regards,

Barry

David and Barry,

Here's a screen shot of your draft Sheet and my changes:

I added the last two columns; one to identify the tach readings for oil changes, and one to identify dates for annual inspections.

These data are used in the alarms and status cells.

Column L formula: =IF(B="Oil Change", C, "")

Column M formula: =IF(B="Annual Inspection", MAX(OFFSET($A$1, 0,0,ROW())), "")

B3: last oil change is: =MAX(L)

C4: current tach is: =MAX(C)

H2: Oil change due is: =B4+50

I2: Inspection due is: =MAX(M)+365 (May need to seed this one for blank sheet case, same for oil change.)

H4: Oil change alarm is: =IF(C4>H2, "Change Oil", "")

I4: Inspection alarm is: =IF(TODAY()>I2, "Inspection Due", "Inspection Due in "&DUR2DAYS(I2-TODAY())&" Days")

I'm sure you will be able to improve on these suggestions.

Regards,

Jerry

Pretty much the same direction I was heading, Jerry. Where did the "Draft table" come from?

Regards,

Barry

Barry wrote:

Pretty much the same direction I was heading, Jerry. Where did the "Draft table" come from?

Regards,

Barry

Barry,

Dave sent me a link to download it from his iWork.com account. I posted the screen shot so you and others could see the layout. Check your email for Subject "Airplane Maintenance Log".

Jerry

Hi Jerry,

Thanks for forwarding the file.

Here's a screen shot of the main table with my changes. Notes and formulas below.

Changes: Every maintenance item is individually dated in column A. The dates are needed only for Oil Change and Annual Inspection rows in the current form, but later versions may require then for similar purposes.

New Columns: L (Oil Change) and M (Annual Inspection)

These use the same formula as Jerry's column L example, with a minor revision:

L5 (and filled down): =IF(B=L$5,C,"")

M5 (and filled down): =IF(B=M$5,A,"")

For each formula, I've placed the comparison strings ("Oil Change" and "Annual Inspection") into one of the header cells for that column, then used a reference to that cell to pick it up.

I misinterpreted what was wanted in the "Last oil change' box (B4) and used the formula below, which returns the date of that change:

B4: =LOOKUP(MAX(L),L,A)

Engine hours at the last change makes more sense, of course, and Jerry's formula, =MAX(L) gives you that.

We used the same formula, =MAX(B) for C4. My number is different here, as I revised the sample data to supply a number that would trigger the conditional formattng rule in H4.

H2: =MAX(L)+50

Different in that I went directly to the data in column L rather than the copy in B4.

H4: =H2-MAX(C)

The result here is the number of engine hours until the next change is due. I wanted a number here in order to have the possibility of a two-stage alarm.

The two conditional format rules below are used so that the alarm cell goes amber (shown) when the oil change is due in 10 hours or fewer, and goes red when the engine hours to the next change is zero or negative.

I4: =DATE(YEAR(MAX(M))+1,MONTH(MAX(M)),DAY(MAX(M)))

More complicated than Jerry's formula, and probably overkill. It's only benefit is to put the annual inspection on the same date in the next year for any year. The date + 365 formula will set a date a day earlier in leap years (which is likely insignificant).

I2: =DUR2DAYS(I4-TODAY())

As in H4, this is intended to get a numerical result to allow the setting of two alarm levels-amber for 10 days or less away, Red for 0 days or past due. The conditional format rules are the same as for H4 (above), but in this case the number represents Days rather than hours. Using this might require a change in labeling of the "Warning" cells.

Incidentally, B4 and C4 are not used in further calculations in this version. They can be kept or deleted as you see fit.

Regards,

Barry

Dave,

If you can put your oil change rules into a verbose form, we can try to formulate an expression to do it in Numbers. If the rule is logical, and unambiguous, it's a good bet that we can give you an answer.

Learning the programming language in Numbers is a matter of practice and learning from others.

Thanks for the feedback.

Jerry

"I am going to work with this and try to set the oil change alarm to go off based on TachTime and Date. One big problem in General aviation motors is the long period of time between engine runs. It is important to change the oil based on both calender time and run time."

Hi Dave,

From your description, the alarm will be dependent on whichever of these conditions reaches the limit first. Based on that assumption, and with the change due after 50 hours or 90 days, whichever comes first, here are the changes and additions I would make to the formulas above.

All changes are in the part of the table shown:

Note that the Oil change alarm will be a single stage alarm, going red when either the current engine hours or date is greater than the limit shown in the cell above the alarm.

"last oil change" is moved up to B2, and the vertical alignment in that cell set to 'bottom.'

B3: =LOOKUP(MAX(L),L,A)

B4: =MAX(L)

These return the Date and Engine Hours reading for the most recent oil change.

C4: as in previous post.

H2:

=MAX(L)+50&" hrs

or

"&LOOKUP(MAX(L),L,A)+90

OR

=MAX(L)+50&" hrs

"&LOOKUP(MAX(L),L,A)+90

The first gives the three line result shown, and requires a slight adjustment in the height of row 2. The second eliminates the middle line ( "or" ) adn the necessity of the row height adjustment.

The line breaks are forced by including an option-return in the formulas after ' " hrs' and after ' or '

H4: =IF(OR(B4+50<=C4,B3+90<=TODAY()),"CHANGE OIL","OK")

This is a straightforward IF(condition,if-true,if-false) function, with a condition statement that is TRUE if either of its parts is TRUE.

Maximum hours between oil changes is set by the "50", maximum days between oil changes by the "90".If either maximum is exceed, the condition is TRUE, and the text "CHANGE OIL" is placed in the cell.

A conditional format rule (below) sets the cell background to red if the cell contains "CHANGE OIL".

Regards,

Barry