Incorrect Counting of IF function

[Guest]

While using Logical functions IF, I’ve found a problem that I wish to share
with Microsoft and other Excel users. Here is a very simple example.
Assuming that my data B1: B4 contains numbers and text. B1=2, B2=5, B3=User,
B4=7. The main task for formula B5: =SUM(IF(B1:B4>0,1)) is to count numbers.
The result of this formula is 4 but it should be 3. It is because
unfortunately this formula counts also text which is incorrect. The logical
test in this case is evaluated to true for numbers bigger than zero and
unfortunately also for any text in cells. As a consequence, the formula B5
counts texts the same way as numbers. To avoid this situation a logical test
with a condition > 0 for data which contains text should be evaluated to
false (not to true like it is done in Excel). Of course I can also easily
eliminate this kind of situation by putting an additional condition but the
point is to eliminate it overall the way I’ve suggested above. Actually
Function IF works that way if any text is assigned to a condition < 0 or
equal zero (Logical test in this case is evaluated to false). I think that
you’d agree with me that if logical test with condition for example User > 0
in the following formula ( the result of this formula is 2) =IF("User">0,2)
is evaluated to true looks a little awkward and beyond that you can see
consequences of creating other formulas.
Thanks for your time.




----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Guest]

Ummmm...Have you checked out the COUNT function.
It counts numbers.

The sum function will treat text cells as zeros so they don't needlessly
corrupt the returned value.

A1: User
A2: =SUM(A1)
returns 0

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[RagDyer]

How about:

=COUNT(B1:B4)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

While using Logical functions IF, I’ve found a problem that I wish to share
with Microsoft and other Excel users. Here is a very simple example.
Assuming that my data B1: B4 contains numbers and text. B1=2, B2=5, B3=User,
B4=7. The main task for formula B5: =SUM(IF(B1:B4>0,1)) is to count numbers.
The result of this formula is 4 but it should be 3. It is because
unfortunately this formula counts also text which is incorrect. The logical
test in this case is evaluated to true for numbers bigger than zero and
unfortunately also for any text in cells. As a consequence, the formula B5
counts texts the same way as numbers. To avoid this situation a logical test
with a condition > 0 for data which contains text should be evaluated to
false (not to true like it is done in Excel). Of course I can also easily
eliminate this kind of situation by putting an additional condition but the
point is to eliminate it overall the way I’ve suggested above. Actually
Function IF works that way if any text is assigned to a condition < 0 or
equal zero (Logical test in this case is evaluated to false). I think that
you’d agree with me that if logical test with condition for example User > 0
in the following formula ( the result of this formula is 2) =IF("User">0,2)
is evaluated to true looks a little awkward and beyond that you can see
consequences of creating other formulas.
Thanks for your time.




----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Guest]

Also...(Yup..I clicked Post too soon)...
The way you're using the formula, Excel will compare the relatiive values of
the arguments:.

A1: aaaaa
A2: bbbbb
A3: 0

A4: A1>A2 (returns FALSE)
A5: A2>A1 (returns TRUE)
A6: A1>A3 (returns TRUE)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Dave Peterson]

How about just using
=count(b1:b4)

It's restricted to just numbers.

 

[vandenberg p]

The others have suggested the count function. Here is another that
literally does what you suggest. It tests for a number and counts only
those cells that evaluate to a number.

One could argue that your problem is caused by the fact that you
are counting numbers and text together without checking to
see if you can really add the "values" in the cells. Certainly one could
argue that since your use of the sum function is "ambiguous"
it might be reasonable that the word "user" should have a value greater
than zero, since the cell will clearly not contain the value of zero
if it contains the value "user".


=SUM(IF(ISNUMBER((B1:B4)),1,0)) (as an array)

Pieter Vandenberg


: While using Logical functions IF, I've found a problem that I
wish to share
: with Microsoft and other Excel users. Here is a very simple
example.
: Assuming that my data B1: B4 contains numbers and text. B1=2,
B2=5, B3=User,
: B4=7. The main task for formula B5: =SUM(IF(B1:B4>0,1)) is to
count numbers.
: The result of this formula is 4 but it should be 3. It is because
: unfortunately this formula counts also text which is incorrect.
The logical
: test in this case is evaluated to true for numbers bigger than
zero and
: unfortunately also for any text in cells. As a consequence, the
formula B5
: counts texts the same way as numbers. To avoid this situation a
logical test
: with a condition > 0 for data which contains text should be evaluated to
: false (not to true like it is done in Excel). Of course I can also easily
: eliminate this kind of situation by putting an additional condition but the
: point is to eliminate it overall the way I've suggested above. Actually
: Function IF works that way if any text is assigned to a condition < 0 or
: equal zero (Logical test in this case is evaluated to false). I think that
: you'd agree with me that if logical test with condition for example
User > 0
: in the following formula ( the result of this formula is 2) =IF("User">0,2)
: is evaluated to true looks a little awkward and beyond that you can see
: consequences of creating other formulas.
: Thanks for your time.

:


: ----------------
: This post is a suggestion for Microsoft, and Microsoft responds to the
: suggestions with the most votes. To vote for this suggestion, click the "I
: Agree" button in the message pane. If you do not see the button, follow this
: link to open the suggestion in the Microsoft Web-based Newsreader and then
: click "I Agree" in the message pane.