Work around for Excel's array formulae?

What is the formula?

Jerry

Hi Rich,

Some made-up dates.

If you have multiple x's in a column, change all but the most recent to "Done" or something other than x This approach looks for the date in Column A that matches a single x in a task column.

This example works on years. I hope you change your oil and filter more often 😉.

One Header Row and four Footer Rows. Enter Years between services (D9) from the keyboard.

To find the date of the last service (the only x in Column D)

D10 contains this formula:

=INDEX($A,MATCH("x",D,0))

You can Fill Right to other columns.

"Years since" (D11) contains this formula:

=YEAR(TODAY())-YEAR(D10)

"Alert" (D12) contains this formula:

=IF(D11>D9,"Hey, You!", "OK")

Conditional Formatting in D12 sets the text to red if it equals "Hey, You!"

It would be best to combine these steps into one complex formula. Numbers is slowed down with each Header or Footer in a Table.

Regards,

Ian.

I hope this post makes it. I have ride three times already.

If you do a LOOKUP for "x" in a column of x's, it will find the lowest one. As long as your columns of dates and hours are in ascending order, that should be all you need to get the most recent date and highest hours.

I do not know what you will do for the rotated text at the top of your table. Numbers does not have that feature. The usual workaround is 90 degree rotated text in text boxes placed over each cell. The cell can have a conditional format, such as one to turn it red, but the text in the text box cannot. It is not a great workaround.

And, of course, spell check gets me again. It was "tried three times".

Rich,

I was able to preserve your format to some degree, but I had to add a field for current hours. I added a field for current date too, just for clarity.

The expressions in Hrs:

=$A$2−LOOKUP("X", D6:D18,$C6:$C18)

The expressions in Yrs:

=DUR2DAYS($A$1−LOOKUP("X", D6:D18,$A6:$A18))÷365

Regards,

Jerry