count previous sequence of zeros

[scml]

hi folks

need some help on excel formula to make it possible to count the previous sequence of zeros if existing

for example considering the above columns I want to get the next results:
0 5 3 0 0 0 1 3 3 0

it's the number of skips a number different from zero occurs in each column
for example in the 1st column the result would be 0 because the last occurance is a number different from zero but in the 3rd column I would get 3 because all the three last occurences are zeros until I get one different from zero value which is in this case a 1

i want to look from bottom to the above

can you help me?

tx in advance

 

[scml]

making it simple:

if last occurence is a zero I want to count it and all the consecutive zeros right before that one

if last occurence is not a zero I do not want to count so I will get zero

 

[T. Valko]

hi folks

need some help on excel formula to make it possible to count the previous sequence of zeros if existing

for example considering the above columns I want to get the next results:
0 5 3 0 0 0 1 3 3 0

it's the number of skips a number different from zero occurs in each column
for example in the 1st column the result would be 0 because the last occurance is a number different from zero but in the 3rd column I would get 3 because all the three last occurences are zeros until I get one different from zero value which is in this case a 1

i want to look from bottom to the above

can you help me?

tx in advance

Try this...

Book1
	A	B	C	D	E	F	G
2	1	0	1	2	0	0	0
3	0	0	1	0	0	0	2
4	0	0	0	1	1	1	0
5	0	0	0	1	0	0	1
6	1	0	0	1	1	0	1
7	_	_	_	_	_	_	_
8	0	5	3	0	0	2	0
Sheet2

This array formula** entered in A8 and copied across:

=LOOKUP(1E100,FREQUENCY(IF(A2:A6=0,ROW(A2:A6)),IF(A2:A6<>0,ROW(A2:A6))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[scml]

thanks a lot

now the problem will be translating english excel formulas to portuguese ones

Try this...

Sheet2

<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:37px;"><col style="width:37px;"><col style="width:37px;"><col style="width:37px;"><col style="width:37px;"><col style="width:37px;"><col style="width:37px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">1</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">0</td></tr></tbody></table>


This array formula** entered in A8 and copied across:

=LOOKUP(1E100,FREQUENCY(IF(A2:A6=0,ROW(A2:A6)),IF(A2:A6<>0,ROW(A2:A6))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[scml]

what is 1E100 ?

tx

 

[T. Valko]

thanks a lot

You're welcome!

now the problem will be translating english excel formulas to portuguese ones

Can't help you with that!

There's another section of the forum that is for questions in other languages. Maybe post the formula in there and ask for a translation.

Good luck!

 

[T. Valko]

what is 1E100 ?

tx

1E100 is scientific notation, a "shorthand" method of writing very large numbers.

1E100 represents the very large number 1 followed by 100 zeros.

How this formula works...

The FREQUENCY function performs a series of counts. In this application we want the last count in the series. To get that we use the LOOKUP function.

In this application...

If all the counts in the series are less than the lookup value 1E100 then LOOKUP will return the last numeric value (counts in the series) that is less than the lookup value.

To ensure that we get the LAST count in the series we (I) use a really gigantic lookup value.

 

[pgc01]

now the problem will be translating english excel formulas to portuguese ones

Hi

Select A8, invoke the VBEditor and in the immediate window write:

ActiveCell.Formula = "=LOOKUP(1E100,FREQUENCY(IF(A2:A6=0,ROW(A2:A6)),IF(A2:A6<>0,ROW(A2:A6))))"

and press return.

Then copy the formula accross.

This is a simple way to translate formulas to the local language.