software

Has anybody encountered a problem with the countblank function? I am using a
formula =if(a21=quot;quot;,quot;quot;,a21); =if(a22=quot;quot;,quot;quot;,a22) etc. I then use the
countblank function to count the number of blanks in this array. If I delete
a21 then the formula delivers the blank cell but the countblank doesn't
increase. Weirdly, if I press delete a second time then the countblank
updates. I've checked the calculation options and everything seems to be set
right so why would this happen?

Thanks

Ian

PS I know that it's easy to get round this by using other counts but I
wondered why the countblank function doesn't work properly.

Are you sure that you are not clearing A21 with the delete key but hitting
the spacebar? Space looks blank, but is not.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Ian Pquot; gt; wrote in message
...
gt; Has anybody encountered a problem with the countblank function? I am
using a
gt; formula =if(a21=quot;quot;,quot;quot;,a21); =if(a22=quot;quot;,quot;quot;,a22) etc. I then use the
gt; countblank function to count the number of blanks in this array. If I
delete
gt; a21 then the formula delivers the blank cell but the countblank doesn't
gt; increase. Weirdly, if I press delete a second time then the countblank
gt; updates. I've checked the calculation options and everything seems to be
set
gt; right so why would this happen?
gt;
gt; Thanks
gt;
gt; Ian
gt;
gt; PS I know that it's easy to get round this by using other counts but I
gt; wondered why the countblank function doesn't work properly.
I've tried it in other worksheets (making sure that I use the delete key) and
I get the same result every time. If I put values in cells A1:A6 and then
use the formula =a1 in cell a18 and =a2 in cell a19 etc and then use the
=countblank(a18:a23) I get the result 0. If I then delete A1 I still get
zero. Press delete again and I get the result 1. Could someone else with
Excel 2000 confirm if this does the same for them?

Thanks

Ian

quot;Bob Phillipsquot; wrote:

gt; Are you sure that you are not clearing A21 with the delete key but hitting
gt; the spacebar? Space looks blank, but is not.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;Ian Pquot; gt; wrote in message
gt; ...
gt; gt; Has anybody encountered a problem with the countblank function? I am
gt; using a
gt; gt; formula =if(a21=quot;quot;,quot;quot;,a21); =if(a22=quot;quot;,quot;quot;,a22) etc. I then use the
gt; gt; countblank function to count the number of blanks in this array. If I
gt; delete
gt; gt; a21 then the formula delivers the blank cell but the countblank doesn't
gt; gt; increase. Weirdly, if I press delete a second time then the countblank
gt; gt; updates. I've checked the calculation options and everything seems to be
gt; set
gt; gt; right so why would this happen?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Ian
gt; gt;
gt; gt; PS I know that it's easy to get round this by using other counts but I
gt; gt; wondered why the countblank function doesn't work properly.
gt;
gt;
gt;

Aaah, now we understand. Countblank won't work because the cells are not
blank, they have a formula. The cells that they refer to may be blank, but
they are not.

Try

=COUNTIF(A18:A23,quot;quot;)

instead--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Ian Pquot; gt; wrote in message
...
gt; I've tried it in other worksheets (making sure that I use the delete key)
and
gt; I get the same result every time. If I put values in cells A1:A6 and then
gt; use the formula =a1 in cell a18 and =a2 in cell a19 etc and then use the
gt; =countblank(a18:a23) I get the result 0. If I then delete A1 I still get
gt; zero. Press delete again and I get the result 1. Could someone else with
gt; Excel 2000 confirm if this does the same for them?
gt;
gt; Thanks
gt;
gt; Ian
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Are you sure that you are not clearing A21 with the delete key but
hitting
gt; gt; the spacebar? Space looks blank, but is not.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;Ian Pquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Has anybody encountered a problem with the countblank function? I am
gt; gt; using a
gt; gt; gt; formula =if(a21=quot;quot;,quot;quot;,a21); =if(a22=quot;quot;,quot;quot;,a22) etc. I then use the
gt; gt; gt; countblank function to count the number of blanks in this array. If I
gt; gt; delete
gt; gt; gt; a21 then the formula delivers the blank cell but the countblank
doesn't
gt; gt; gt; increase. Weirdly, if I press delete a second time then the
countblank
gt; gt; gt; updates. I've checked the calculation options and everything seems to
be
gt; gt; set
gt; gt; gt; right so why would this happen?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; Ian
gt; gt; gt;
gt; gt; gt; PS I know that it's easy to get round this by using other counts but I
gt; gt; gt; wondered why the countblank function doesn't work properly.
gt; gt;
gt; gt;
gt; gt;
quot;Bob Phillipsquot; gt; wrote in message
...
gt; quot;Ian Pquot; gt; wrote in message
gt; ...
gt;gt; quot;Bob Phillipsquot; wrote:

gt;gt; gt; quot;Ian Pquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt; gt; Has anybody encountered a problem with the countblank function? I am
gt;gt; gt; using a
gt;gt; gt; gt; formula =if(a21=quot;quot;,quot;quot;,a21); =if(a22=quot;quot;,quot;quot;,a22) etc. I then use the
gt;gt; gt; gt; countblank function to count the number of blanks in this array. If
gt;gt; gt; gt; I
gt;gt; gt; delete
gt;gt; gt; gt; a21 then the formula delivers the blank cell but the countblank
gt; doesn't
gt;gt; gt; gt; increase. Weirdly, if I press delete a second time then the
gt; countblank
gt;gt; gt; gt; updates. I've checked the calculation options and everything seems
gt;gt; gt; gt; to
gt; be
gt;gt; gt; set
gt;gt; gt; gt; right so why would this happen?
gt;gt; gt; gt;
gt;gt; gt; gt; Thanks
gt;gt; gt; gt;
gt;gt; gt; gt; Ian
gt;gt; gt; gt;
gt;gt; gt; gt; PS I know that it's easy to get round this by using other counts but
gt;gt; gt; gt; I
gt;gt; gt; gt; wondered why the countblank function doesn't work properly.

gt;gt; gt; Are you sure that you are not clearing A21 with the delete key but
gt; hitting
gt;gt; gt; the spacebar? Space looks blank, but is not.

gt;gt; I've tried it in other worksheets (making sure that I use the delete key)
gt; and
gt;gt; I get the same result every time. If I put values in cells A1:A6 and
gt;gt; then
gt;gt; use the formula =a1 in cell a18 and =a2 in cell a19 etc and then use the
gt;gt; =countblank(a18:a23) I get the result 0. If I then delete A1 I still get
gt;gt; zero. Press delete again and I get the result 1. Could someone else
gt;gt; with
gt;gt; Excel 2000 confirm if this does the same for them?

gt; Aaah, now we understand. Countblank won't work because the cells are not
gt; blank, they have a formula. The cells that they refer to may be blank, but
gt; they are not.
gt;
gt; Try
gt; =COUNTIF(A18:A23,quot;quot;)
gt; instead

How would that explain the second delete in A1 affecting the COUNTBLANK?
[But the second delete doesn't affect the COUNTBLANK for me, with Excel
2003, so I don't know why it does for Ian with 2000.]
--
David Biddulph
The thing I don't understand (at risk of boring everyone with this) is why
when I delete A1, countblank remains 0 but when I press delete a second time
(i.e. press delete in the empty cell) then the formula suddenly kicks in and
the function counts my cell (with a formula in it ) as a blank. I can then
put a value back in A1 and the count goes back to zero. Press delete again
this time it works properly and I get a 1. I can then toggle between a value
and a blank cell and the formula works every time. I know there's a way
around this but I just wonder if this is a known fault.

Ian

quot;Bob Phillipsquot; wrote:

gt; Aaah, now we understand. Countblank won't work because the cells are not
gt; blank, they have a formula. The cells that they refer to may be blank, but
gt; they are not.
gt;
gt; Try
gt;
gt; =COUNTIF(A18:A23,quot;quot;)
gt;
gt; instead
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;Ian Pquot; gt; wrote in message
gt; ...
gt; gt; I've tried it in other worksheets (making sure that I use the delete key)
gt; and
gt; gt; I get the same result every time. If I put values in cells A1:A6 and then
gt; gt; use the formula =a1 in cell a18 and =a2 in cell a19 etc and then use the
gt; gt; =countblank(a18:a23) I get the result 0. If I then delete A1 I still get
gt; gt; zero. Press delete again and I get the result 1. Could someone else with
gt; gt; Excel 2000 confirm if this does the same for them?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Ian
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Are you sure that you are not clearing A21 with the delete key but
gt; hitting
gt; gt; gt; the spacebar? Space looks blank, but is not.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove xxx from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Ian Pquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Has anybody encountered a problem with the countblank function? I am
gt; gt; gt; using a
gt; gt; gt; gt; formula =if(a21=quot;quot;,quot;quot;,a21); =if(a22=quot;quot;,quot;quot;,a22) etc. I then use the
gt; gt; gt; gt; countblank function to count the number of blanks in this array. If I
gt; gt; gt; delete
gt; gt; gt; gt; a21 then the formula delivers the blank cell but the countblank
gt; doesn't
gt; gt; gt; gt; increase. Weirdly, if I press delete a second time then the
gt; countblank
gt; gt; gt; gt; updates. I've checked the calculation options and everything seems to
gt; be
gt; gt; gt; set
gt; gt; gt; gt; right so why would this happen?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt; gt; gt; gt; Ian
gt; gt; gt; gt;
gt; gt; gt; gt; PS I know that it's easy to get round this by using other counts but I
gt; gt; gt; gt; wondered why the countblank function doesn't work properly.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

I am using 2000 and I just tested this. It does not happen the same way for
me as it does for you. When I use countblank on a range that has formulas
that return quot;quot; if the cell they refer to is blank, then as soon as I delete
the cell that the above mentioned formulas refer to, the countblank increases
by 1. First I tried a countblank formula which was located on a row
underneath the range I was testing. I also tested it on a column/row which
was before the cells being tested and both times, the countblank updated as
soon as I hit delete.
--
Kevin Vaughnquot;Ian Pquot; wrote:

gt; I've tried it in other worksheets (making sure that I use the delete key) and
gt; I get the same result every time. If I put values in cells A1:A6 and then
gt; use the formula =a1 in cell a18 and =a2 in cell a19 etc and then use the
gt; =countblank(a18:a23) I get the result 0. If I then delete A1 I still get
gt; zero. Press delete again and I get the result 1. Could someone else with
gt; Excel 2000 confirm if this does the same for them?
gt;
gt; Thanks
gt;
gt; Ian
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Are you sure that you are not clearing A21 with the delete key but hitting
gt; gt; the spacebar? Space looks blank, but is not.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove xxx from email address if mailing direct)
gt; gt;
gt; gt; quot;Ian Pquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Has anybody encountered a problem with the countblank function? I am
gt; gt; using a
gt; gt; gt; formula =if(a21=quot;quot;,quot;quot;,a21); =if(a22=quot;quot;,quot;quot;,a22) etc. I then use the
gt; gt; gt; countblank function to count the number of blanks in this array. If I
gt; gt; delete
gt; gt; gt; a21 then the formula delivers the blank cell but the countblank doesn't
gt; gt; gt; increase. Weirdly, if I press delete a second time then the countblank
gt; gt; gt; updates. I've checked the calculation options and everything seems to be
gt; gt; set
gt; gt; gt; right so why would this happen?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; Ian
gt; gt; gt;
gt; gt; gt; PS I know that it's easy to get round this by using other counts but I
gt; gt; gt; wondered why the countblank function doesn't work properly.
gt; gt;
gt; gt;
gt; gt;