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.
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.