Counting Values Backwards!

[cguy3000]

Alright folks!! I have searched and searched for this and I have found no solution to my problem anywhere on the interwebs!

So I have a list of data, simply just rows of numbers say,

2
3
7
4
4
0
1
2
0
3
42
1

I am trying to find a way for excel to start from the bottom of the data and count upwards until the number 0 is reached. So for example, for the data set I provided at the above I would need some sort of equation to give a value of 3 because there are 3 values until the last 0 is reached countin gfom the bottom up. And if 0 is in the most bottom spot in the data column then it would return a value of 0.

I thought some variation of the count or countif equations would do the trick but I have no idea how to get it to count backwards. An equation that I could have resting in its own cell and always be checking so as soon as I update the column then it would update iteself too inorder to incorporate the new value.


Thanks you all!

 

[shg]

=match(9e+307,a:a ) - lookup(9e+307, row(a1:a12) / (a1:a12=0))

 

[cguy3000]

So if I have data values from A98 to A344 what would that equation you have look like? I need it to be in a range as well and not the entire A column. Sorry I didnt mention that!

 

[cguy3000]

When I do it for just those 12 values it works fine! However whever I change the range to A98 to A344 it gives me a value of 15 even though the most bottom cell is 0 so it should be returning a value of 0. Any ideas?

 

[cguy3000]

Also, another issue I forgot to mention. Every week I will be adding a new blank row at the bottom of the data set as to continue draggin down the range for all my other equations to incorperate the new data point. Because if I reference the actual last point rather than the next empty cell then the range of the equation doesnt move with the continual adding of numbers and dates. So Id have to manually drag down the range which would defeat the purpose. Would a macro be better equiped to handle this?

 

[Marcelo Branco]

See if this does what you need


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

97​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

98​

[/td][td]

2​

[/td][td][/td][td]

LastZero position​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

99​

[/td][td]

3​

[/td][td][/td][td]

9​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

100​

[/td][td]

7​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

101​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

102​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

103​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

104​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

105​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

106​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

107​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

108​

[/td][td]

42​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

109​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

110​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in C99
=LOOKUP(2,1/((A98:A10000=0)*(A98:A10000<>"")),ROW(A98:A10000)-ROW(A98)+1)

Formula in D99
=COUNTIFS(INDEX(A98:A1000,C99):A10000,"<>0",INDEX(A98:A10000,C99):A10000,"<>")

M.