Count blanks cells

[jmumby]

Hi there,

Just wondering if anyone knows of a way to count blank cells on a sheet
then place the result in the cell it was counted from. Assumeing it has
a result itself.

In each cell I have have =IF(COUNTIF(A7:B7:C77:E7:F7, "1")=1,"1","
"). Looking to count how far between common results.

for example

+-----------+
| 1 |
+-----------+
| |
+-----------+
| |
+-----------+
| |
+-----------+
| |
+-----------+
| 4 |
+-----------+

I have had a look at COUNTBLANK but I don't think thats going to work
for me.

Thanks!

Jason

 

[Guest]

I'm not sure of how the rest of your sheet looks, but I'm going to assume
that at row 7 you have something like
A B C D E F G H I J K L
1 2 3 1 2 3 3 2 1 1 2 3

Then I will assume that you will type the value to find blanks between in
cell A16
Then in cell B16 put this formula in:
=IF(ISERROR(IF(MATCH($A16,B$7:$L$7,0)-MATCH($A16,A$7:$L$7,0)<0,"",MATCH($A16,B$7:$L$7,0)-MATCH($A16,A$7:$L$7,0))),"",IF(MATCH($A16,B$7:$L$7,0)-MATCH($A16,A$7:$L$7,0)<0,"",MATCH($A16,B$7:$L$7,0)-MATCH($A16,A$7:$L$7,0)))

Extend that formula over to column K (one short of end of your data column)
and numbers will appear showing spaces between occurances of the number you
entered in cell A16. When numbers are next to one another, like 1 in columns
I and J, it will show zero (0). When matches aren't found , no entry will be
displayed.

The way it is written you can also extend it down the sheet and it will
always refer to row 7, but allow you to enter different numbers in column A
to examine several sets of spacings for different values.

I'm not certain this is exactly what you are looking for, but it's what I
envisioned you as looking for, at least to some degree. At least maybe it
will give you some more ideas. Check Excel Help for the MATCH() function to
see how it works.

The ISERROR() is in there to keep from displaying #NA errors when no match
at all is found, and the check for <0 is in there because in cases with
adjacent cells with the same value, you can end up with a negative number.

 

[jmumby]

Thanks for the reply!

I might have confused things a bit!

My sheet looks like this.

+ A B C D E F G H I J K L M O P.....
*1* 1 2 3 4 3 3 2
*2* 8 4 2 5 6 3 5
*3* 9 3 4 5 2 7 5
*4* 8 6 7 1........

And repeats with random numbers down the spread sheet for about 900
rows. In column H or I the code I had done
=IF(COUNTIF(A7:B7:C77:E7:F7, "1")=1,"1"," it would put in a 1 if it
occured in the row. In the next two rows it would be blank (no 1 in
those rows). In the 4th row down it has a one but in row H I want it to
put in '2' counting the two blank cells above.

It gets worse, in the next column I have =IF(COUNTIF(A7:B7:C77:E7:F7,
"1")=2,"1"," so would like this to do the same except obviously for 2.

I think this may be a bit out excels realm but it would be interesting
to see if it could!

Thanks,

Jason

 

[Domenic]

Assumptions:

Columns A through G, starting with Row 2, contain the data

H1 and I1 contain the target numbers 1 and 2

The target number can occur more than once in any row

Defined Name:

Select H2

Insert > Name > Define

Name: Array

Refers to:

=(MMULT(--($A$2:$G2=H$1),TRANSPOSE(COLUMN($A$2:$G2)^0))>0)+0

Click Ok

Formula:

H2, copied down and across:

=IF(ISNUMBER(MATCH(H$1,$A2:$G2,0)),IF(SUM(Array)>1,ROWS(H$2:H2)-LARGE(IF(
Array,ROW($A$2:$G2)-ROW($A$2)+1),2)-1,1),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[jmumby]

Hey,

Thanks for your help!

I am pretty sure I followed your instructions word for word but I jus
seem to get blank cells now?

You can find the actual spreadsheet her
http://labtrack.dpn.homeip.net/number_thing.zip
if you get an opportunity perhaps you can tell me where I am goin
wrong?

Thanks,

Jaso

 

[Domenic]

It looks like for some reason you've entered the formula in an array of
cells. Instead, enter the formula in H2 only, confirm with
CONTROL+SHIFT+ENTER, and then copy/drag down and across. Also, since
Column G contains no data, adjust the ranges accordingly. Post back if
you need further help...

 

[jmumby]

Hey,

Your the man!

It was just a case of me not entering the formula properly.

Thanks for your help!

Jaso