Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting number of consecutive zeros at the end of a list
I have a column of numbers like this:
5 7 0 5 0 0 Empty Cell Empty Cell And I want to count the number of consecutive zeros at the end of the list ignoring any blank cells. In the above example the result would be "2" - two consecutive zeros and the two blank cells are ignored. Thanks for any help you can provide. if the last non-blank cell is any value except zero then the result shound be "0". |
#2
|
|||
|
|||
Try this
=MAX((A2:A20<"")*(ROW(A2:A20)))-MAX((A2:A20<0)*(A2:A20<"")*(ROW(A2:A20))) it is an array formula, so commit with Ctrl-Shift-Enter. -- HTH Bob Phillips wrote in message oups.com... I have a column of numbers like this: 5 7 0 5 0 0 Empty Cell Empty Cell And I want to count the number of consecutive zeros at the end of the list ignoring any blank cells. In the above example the result would be "2" - two consecutive zeros and the two blank cells are ignored. Thanks for any help you can provide. if the last non-blank cell is any value except zero then the result shound be "0". |
#3
|
|||
|
|||
Try...
=COUNTIF(INDEX(A1:A100,IF(COUNTIF(A1:A100,"0"),MA TCH(2,1/(A1:A1000)),1) ):INDEX(A1:A100,MATCH(2,1/(A1:A100<""))),0) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article .com, wrote: I have a column of numbers like this: 5 7 0 5 0 0 Empty Cell Empty Cell And I want to count the number of consecutive zeros at the end of the list ignoring any blank cells. In the above example the result would be "2" - two consecutive zeros and the two blank cells are ignored. Thanks for any help you can provide. if the last non-blank cell is any value except zero then the result shound be "0". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Counting number of occurrences | Excel Worksheet Functions | |||
Counting the total number of cells with specified condition(freque | Excel Discussion (Misc queries) | |||
Counting number of grades in a row | Excel Worksheet Functions | |||
counting number of particular items in a list | Excel Discussion (Misc queries) |