Night Vision Goggle Tracker

[bobsmith58d]

Hi, can anyone help me please?

Given:
1) I have a column of numbers, some of which are zero and some that ar
non-zero.
2) I have a column of dates (ie, 4 sep, 5 sep, etc) that correlate wit
data in the column of numbers.

example:

Date Value
4Sep 1.3
5Sep 0
6Sep .8
7Sep 0
8Sep 0
9Sep 2.0
10Sep 0

I want to find the number of days that have passed since Today and th
last non-zero value in the column of numbers.

Note: I understand how to use: days365(today(), DATE)

The problem, is determining which date corresponds to the las
non-zero value in the column, and then inserting that row number int
the above function. In this example, I would want the function t
return the number of days between Today and 9Sep.
Another twist: I will be constantly adding entries to thi
spreadsheet, so the date that corresponded to the last non-zero valu
will change. I don't know if that makes things harder or not.

I am forever indebted to whoever can help me out. I am an Army pilo
and I built a spreadsheet to track my day/night/NVG hours and want t
track how many days have passed since I last flew Night Vision Goggles
Thank You!!! :

 

[Alan]

message

Hi, can anyone help me please?

Given:
1) I have a column of numbers, some of which are zero and some that
are non-zero.
2) I have a column of dates (ie, 4 sep, 5 sep, etc) that correlate
with data in the column of numbers.

example:

Date Value
4Sep 1.3
5Sep 0
6Sep .8
7Sep 0
8Sep 0
9Sep 2.0
10Sep 0

I want to find the number of days that have passed since Today and the
last non-zero value in the column of numbers.

Note: I understand how to use: days365(today(), DATE)

The problem, is determining which date corresponds to the last
non-zero value in the column, and then inserting that row number into
the above function. In this example, I would want the function to
return the number of days between Today and 9Sep.
Another twist: I will be constantly adding entries to this
spreadsheet, so the date that corresponded to the last non-zero value
will change. I don't know if that makes things harder or not.

I am forever indebted to whoever can help me out. I am an Army pilot
and I built a spreadsheet to track my day/night/NVG hours and want to
track how many days have passed since I last flew Night Vision
Goggles. Thank You!!!

Hi Bob,

One way might be to add a third column, multiplying the date value in
the first column by the boolean value (second column <> 0).

Next, use the MAX function to find the latest date with a non-zero
value

Finally, VLOOKUP or MATCH / OFFSET to return the date value associated
with that MAX value.


HTH,

Alan.