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.

	A	B
1	28-Mar-13	22000
2	29-Mar-13	0
3	1-Apr-13	0

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.

[shukla.ankur281190]

Hi Welcome to Excel Forum,

Can you please upload your sample workbook clean you confidential information and attached a sample with expect result.

Go to advance and click on paper clip icon and attach workbook.

[FlameRetired]

It is best to avoid referencing whole columns, but try this

Please Login or Register  to view this content.

It is best to use references that are a bit larger than anticipated need. If you think your data will have about 10,000 rows make the range B1:B15000.

[samcoarse]

Hi FlameRetired,

This worked perfectly.

Amazing that I can strain over this for hours (first week using Excel) and an elegant, more streamline answer can come back in minutes.

Appreciate your efforts.

[FlameRetired]

samcoarse,

Thank you for the feedback and kind words. Glad to help.

Also I overlooked this was your first post. Welcome to the forum.