Countdown cells until an event in adjacent column

[zeke333]

How do I countdown the number of cells until an event in an adjacent column? I only want to look 7 days ahead.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]IRRIGATE (GALLONS)[/TD]
[TD]DAYS UNTIL IRRIGATE[/TD]
[/TR]
[TR]
[TD]1/1/2010[/TD]
[TD]--[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1/2/2010[/TD]
[TD]--[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/3/2010[/TD]
[TD]--[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/4/2010[/TD]
[TD]--[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/5/2010[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1/6/2010[/TD]
[TD]--[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/7/2010[/TD]
[TD]--[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/8/2010[/TD]
[TD]--[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/9/2010[/TD]
[TD]520[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1/10/2010[/TD]
[TD]--[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/11/2010[/TD]
[TD]--[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/12/2010[/TD]
[TD]--[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/13/2010[/TD]
[TD]210[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]cont...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi
Welcome to the board

What are the "--" that appear in your table

- are they the text value "--"
or
- are they for ex. the number zero that you hid with a number format?

 

[zeke333]

Treat them as zeros, a zero hidden as "--", or a text value, whichever way is easier for you to present a solution.

They are the result of an equation that either specifies an irrigation volume or not. I can make them show up as 0 if it makes a difference in your solution.

Hi
Welcome to the board

What are the "--" that appear in your table

- are they the text value "--"
or
- are they for ex. the number zero that you hid with a number format?

 

[pgc01]

If the "--" is a real text value then you just have to look for the next number value, like in C2:

=MATCH(TRUE,INDEX(ISNUMBER(B2:$B$1000),),0)-1

Copy the formula down


if, however, the "--" is just a number format and the cell value is the number value zero, then look for the next number value bigger than zero, like in C2:

=MATCH(TRUE,INDEX(B2:$B$1000>0,),0)-1

Copy the formula down

Remark:
Amend the B2:$B$1000 range if 1000 rows is not convenient to your case.

 

[zeke333]

Excellent, Thank you!

 

[pgc01]

You're welcome.