Same formula, different results depending on machine?

[Ken Johnson]

I used the following formula to define a named range for a chart...

=OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4)

On my home PC and both PCs in my staffroom (all Excel 2003) everything
worked perfectly.

However, on a laptop (Excel 2000), the range plotted by the chart was
wrong and I kept getting a message about having more than 32000
points.

Thinking it was a 2000 versus 2003 difference, I swapped to another
laptop with Excel 2003, but the problem remained.

I tracked the problem down to the COUNTIF(Sheet1!$C:$C,"") formula.
On my home and staffroom PCs it counted cells that were blank and
cells rendered blank ("") by an IF formula.

I've had to change the IF formula so that cells I don't want in the
named range are changed to "NO" instead of "" and the formula that
defines the named range is now...

=OFFSET(Sheet1!$C$4,1,0,COUNTIF(Sheet1!$C$5:$C$1005,"<>NO")-1)

Does anybody know why COUNTIF(Sheet1!$C:$C,"") would produce different
results on different machines?

Ken Johnson

 

[Guest]

Hi Ken,

Doesn't work in xl2002 either. However does work in xl2007. However, the
answer to your problems will probably be solved if you use the following:-

COUNTBLANK($C$C)

Regards,

OssieMac

 

[Ken Johnson]

Hi Ken,

Doesn't work in xl2002 either. However does work in xl2007. However, the
answer to your problems will probably be solved if you use the following:-

COUNTBLANK($C$C)

Regards,

OssieMac

Hi OssieMac,

It's not so much a problem, I have changed the formula to get the
desired result, I was just curious as to why the COUNTIF formula would
give different results on different machines. To me, it just doesn't
make sense.

Ken Johnson

 

[Ken Johnson]

Hi Ken,

Doesn't work in xl2002 either. However does work in xl2007. However, the
answer to your problems will probably be solved if you use the following:-

COUNTBLANK($C$C)

Regards,

OssieMac

Hi OssieMac,

I tried your suggestion and it works well.

Thanks for that.

Ken Johnson

 

[Guest]

Hi again Ken,

I thought later why not use COUNTA and count the cells that actually have
data in them?

Regards,

OssieMac

 

[Ken Johnson]

Hi again Ken,

I thought later why not use COUNTA and count the cells that actually have
data in them?

Regards,

OssieMac

Hi OssieMac,

COUNTA unfortunately also counts blank cells that contain a formula
even though that formula has rendered the cell blank by returning the
"" value.

Ken Johnson

Ken Johnson

 

[Guest]

O.K. Ken that's a good point.

Just a little more info that I have now found is that in xl2002 if you put
COUNTIF(C:C,"") into a new worksheet then it comes up with zero. However, if
you enter some data in some of the cells then it still returns zero but if
you delete the data entered (even using Clear All), it then counts the number
of cells that had the data deleted.

I guess that this means that it is not reliable using COUNTIF() to count
blank cells and that is probably the reason for having COUNTBLANK().

I also have xl2007 and COUNTIF(C:C,"") seems to works O.K.

Regards,

OssieMac

 

[Ken Johnson]

O.K. Ken that's a good point.

Just a little more info that I have now found is that in xl2002 if you put
COUNTIF(C:C,"") into a new worksheet then it comes up with zero. However, if
you enter some data in some of the cells then it still returns zero but if
you delete the data entered (even using Clear All), it then counts the number
of cells that had the data deleted.

I guess that this means that it is not reliable using COUNTIF() to count
blank cells and that is probably the reason for having COUNTBLANK().

I also have xl2007 and COUNTIF(C:C,"") seems to works O.K.

Regards,

OssieMac

Hi OssieMac,

Very interesting.

I also notice with xl2000 COUNTIF(C:C,"") on a new worksheet returns
1. Then, after typing ="" into C12 (for example) it returns 12, so it
counts C12 and all the blank cells above it and ignores the blanks
below C12.

Looks like COUNTIF with the "" condition is to be avoided. COUNTBLANK
is the one to use.

Maybe the laptop with xl2003 that also had the problem was missing
some update.

Thanks again.

Ken Johnson

 

[Harlan Grove]

....

Learn to snip.

...>"Ken Johnson" wrote:
...>>I used the following formula to define a named range for a chart...
...>>
...>>=OFFSET(Sheet1!$C$4,1,0,ROWS(Sheet1!$C:$C)-COUNTIF(Sheet1!$C:$C,"")-4)

Are you graphing numeric values? Would all the numeric values in col C of
worksheet Sheet1 appear first, in the topmost rows, then cells evaluating to
"" and finally blank cells? If so, use COUNT. Since it appears you want this
range beginning in Sheet1!C5 (since the 1st arg base range is C4 and the 2nd
arg is 1, that necessarily makes the topmost cell of the derived range
Sheet1!C5) and spanning 4 less than the number of nonblank/non-"" rows, and
assuming those 4 rows were text/heading in the top 4 rows, why not just use

=OFFSET(Sheet1!$C$5,0,0,COUNT(Sheet1!$C:$C))

?

...>>On my home PC and both PCs in my staffroom (all Excel 2003)
...>>everything worked perfectly.

As it should have. XL2003 or one of its service packs eliminated COUNTIF and
SUMIF implicitly restricting their range arguments to their range arguments'
worksheets' used ranges.

...>>However, on a laptop (Excel 2000), the range plotted by the chart was
...>>wrong and I kept getting a message about having more than 32000 points.

This was pre-Excel 2003 SP# functionality - restricting COUNTIF and SUMIF to
worksheets' used ranges.

...>>Thinking it was a 2000 versus 2003 difference, I swapped to another
...>>laptop with Excel 2003, but the problem remained.
....

Are you sure the second laptop had the same service packs applied as the
first two machines?

I also notice with xl2000 COUNTIF(C:C,"") on a new worksheet returns
1. Then, after typing ="" into C12 (for example) it returns 12, so it
counts C12 and all the blank cells above it and ignores the blanks
below C12.

Yup, used range restriction.

Looks like COUNTIF with the "" condition is to be avoided. COUNTBLANK
is the one to use.

Not quite. In XL2000 COUNTIF with *ANY* criteria would have ignored rows
outside the used range. In a new worksheet, enter the following formula in
cell A1.

A1: =COUNTIF(C:C,"<>1")

It'll return 1. Enter 0 in cell C7, the A1 formula will return 7. Change the
format of cell X99. This time you may need to press [F9], but once recalced
A1 will return 99. Point is this has nothing specifically to do with the ""
criterion.

Maybe the laptop with xl2003 that also had the problem was missing
some update.

Most likely.

 

[Ken Johnson]

Hi Harlan,

"Harlan Grove" wrote:

Are you graphing numeric values? Would all the numeric values in col C of
worksheet Sheet1 appear first, in the topmost rows, then cells evaluating to
"" and finally blank cells? If so, use COUNT. Since it appears you want this
range beginning in Sheet1!C5 (since the 1st arg base range is C4 and the 2nd
arg is 1, that necessarily makes the topmost cell of the derived range
Sheet1!C5) and spanning 4 less than the number of nonblank/non-"" rows, and
assuming those 4 rows were text/heading in the top 4 rows, why not just use

=OFFSET(Sheet1!$C$5,0,0,COUNT(Sheet1!$C:$C))

That's almost how it's set up. However, only one of the four top cells
is a heading. The other three are numeric precedents of the column C
formulas. I change their values to change the chart data, so I'm now
using your simpler formula with a slight adjustment...

=OFFSET(Sheet1!$C$5,0,0,COUNT(Sheet1!$C:$C)-3)

Are you sure the second laptop had the same service packs applied as the
first two machines?

The first two machines are on my school's network and would almost
certainly have all updates installed. The laptop with xl2003 and the
problem is a colleague's and it seems likely she has not bothered with
any updates. I'll encourage her to do something about it. It might
even fix up the machine's lack of speed that she's been complaining
about, even though she has done a defrag.


Thanks for your suggestion and the info about COUNTIF.

Learn to snip

I'll keep working on my snipping

Ken Johnson