Extending Formula: Counting days back to last value

[samcoarse]

Hi All,

I am trying to extend a formula that I've written that would allow me to count the days including & before the last date in a column where there was last a value above 0 in a column. Eg: If the last date was 1-Apr-13, in the below table, the number of days before there was a value in column B would be 2.

To note; I will be importing a list with dates that vary in length, due to public holidays/ weekends/ differing start & end dates, so the range cannot be fixed, it will have to find the last value in the list and work backwards from there.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]28-Mar-13[/TD]
[TD]22000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]29-Mar-13[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1-Apr-13[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I've found half a solution, in that a formula I have created will show me if there has been no occurrence on the last & second last day on a column, with no range constraints.

The formula will show me a result of '0' if the last date (A3) in Column A has any corresponding value above 0 in B.

It will show me '1' if there was a 0 value in B3, but a value above 0 in the corresponding cell in column B for date before the last date (A2) exists.

It will show a '2+' if the formula returns a 0 value in B3 & B2.

I'm unsure how to extend this for one more day without the formula tripping up over itself and only presenting '2+' despite there being values in B to the end of the column, or in other words, against the last date. Optimally, I would like to run the formula back to the last time there was a value in column B that was greater than 0. If this is too time consuming, reaching 5+ days would be acceptable.

Here's my rather inelegant formula - "=IFERROR(IF(1=(IF(0<(LOOKUP(2,1/(B:B<>""),B:B))/((LOOKUP(2,1/(B:B<>"")))), 0,1)),(IF(1=(LOOKUP((SUM((MAX(A:A))-1)),A:A,B:B)/(LOOKUP(SUM((MAX(A:A))-1),A:A,B:B))),1,0)),0),"+2")"

If anyone has a more streamlined way of coming to the same conclusion, or would know how to build on this, let me know.

Thanks in advance.

 

[Peter_SSs]

Eg: If the last date was 1-Apr-13, in the below table, the number of days before there was a value in column B would be 2.

Do you mean the number of days as stated, or the number of rows in the table. The number of days from 28 Mar to 1 Apr would be more than 2 by may calculation.

 

[samcoarse]

Do you mean the number of days as stated, or the number of rows in the table. The number of days from 28 Mar to 1 Apr would be more than 2 by may calculation.

Rows in the table. The days in column A are the days that are not weekends/public holidays, during which, a value in B can occur.

 

[Peter_SSs]

Is this what you mean then? Formula in C2 is copied down.

Excel Workbook
	A	B	C
1
2	28-Mar-13	22000	0
3	29-Mar-13	0	1
4	1-Apr-13	0	2
5	2-Apr-13	0	3
6	3-Apr-13	100	0
7	4-Apr-13	200	0
8	5-Apr-13	2000	0
9	8-Apr-13	0	1
10	9-Apr-13	0	2
11	10-Apr-13	0	3
12	11-Apr-13	0	4
13	12-Apr-13	3000	0
14	15-Apr-13	0	1
Days since

 

[samcoarse]

Is this what you mean then? Formula in C2 is copied down.

Days since

	A	B	C

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"><col style="width:54px;"><col style="width:33px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]28-Mar-13[/TD]
[TD="align: right"]22000[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]29-Mar-13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]1-Apr-13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2-Apr-13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]3-Apr-13[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]4-Apr-13[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]5-Apr-13[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]8-Apr-13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]9-Apr-13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]10-Apr-13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]11-Apr-13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]12-Apr-13[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]15-Apr-13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =ROWS(B$2:B2)-AGGREGATE(14,6,(ROW(B$2:B2)-ROW(B$2)+1)/(B$2:B2>0),1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This worked as requested.

Thanks for your assistance on this one, Peter_SSs

 

[Peter_SSs]

This worked as requested.

Thanks for your assistance on this one, Peter_SSs

You are welcome. Thanks for letting us know.