software

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:B4gt;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 gt; 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 lt; 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 gt; 0
in the following formula ( the result of this formula is 2) =IF(quot;Userquot;gt;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 quot;I
Agreequot; 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 quot;I Agreequot; in the message pane.

www.microsoft.com/office/comm...et.f unctions

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-Proquot;kaz ogonowskiquot; wrote:

gt; While using Logical functions IF, I’ve found a problem that I wish to share
gt; with Microsoft and other Excel users. Here is a very simple example.
gt; Assuming that my data B1: B4 contains numbers and text. B1=2, B2=5, B3=User,
gt; B4=7. The main task for formula B5: =SUM(IF(B1:B4gt;0,1)) is to count numbers.
gt; The result of this formula is 4 but it should be 3. It is because
gt; unfortunately this formula counts also text which is incorrect. The logical
gt; test in this case is evaluated to true for numbers bigger than zero and
gt; unfortunately also for any text in cells. As a consequence, the formula B5
gt; counts texts the same way as numbers. To avoid this situation a logical test
gt; with a condition gt; 0 for data which contains text should be evaluated to
gt; false (not to true like it is done in Excel). Of course I can also easily
gt; eliminate this kind of situation by putting an additional condition but the
gt; point is to eliminate it overall the way I’ve suggested above. Actually
gt; Function IF works that way if any text is assigned to a condition lt; 0 or
gt; equal zero (Logical test in this case is evaluated to false). I think that
gt; you’d agree with me that if logical test with condition for example User gt; 0
gt; in the following formula ( the result of this formula is 2) =IF(quot;Userquot;gt;0,2)
gt; is evaluated to true looks a little awkward and beyond that you can see
gt; consequences of creating other formulas.
gt; Thanks for your time.
gt;
gt;
gt;
gt;
gt; ----------------
gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; Agreequot; button in the message pane. If you do not see the button, follow this
gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; click quot;I Agreequot; in the message pane.
gt;
gt; www.microsoft.com/office/comm...et.f unctions

How about:

=COUNT(B1:B4)

--
Regards,

RD

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

quot;kaz ogonowskiquot; gt; wrote in message
...
gt; While using Logical functions IF, I’ve found a problem that I wish to
share
gt; with Microsoft and other Excel users. Here is a very simple example.
gt; Assuming that my data B1: B4 contains numbers and text. B1=2, B2=5,
B3=User,
gt; B4=7. The main task for formula B5: =SUM(IF(B1:B4gt;0,1)) is to count
numbers.
gt; The result of this formula is 4 but it should be 3. It is because
gt; unfortunately this formula counts also text which is incorrect. The
logical
gt; test in this case is evaluated to true for numbers bigger than zero and
gt; unfortunately also for any text in cells. As a consequence, the formula B5
gt; counts texts the same way as numbers. To avoid this situation a logical
test
gt; with a condition gt; 0 for data which contains text should be evaluated to
gt; false (not to true like it is done in Excel). Of course I can also easily
gt; eliminate this kind of situation by putting an additional condition but
the
gt; point is to eliminate it overall the way I’ve suggested above. Actually
gt; Function IF works that way if any text is assigned to a condition lt; 0 or
gt; equal zero (Logical test in this case is evaluated to false). I think that
gt; you’d agree with me that if logical test with condition for example User gt;
0
gt; in the following formula ( the result of this formula is 2)
=IF(quot;Userquot;gt;0,2)
gt; is evaluated to true looks a little awkward and beyond that you can see
gt; consequences of creating other formulas.
gt; Thanks for your time.
gt;
gt;
gt;
gt;
gt; ----------------
gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; Agreequot; button in the message pane. If you do not see the button, follow
this
gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; click quot;I Agreequot; in the message pane.
gt;
gt;
www.microsoft.com/office/comm...et.f unctionsAlso...(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: A1gt;A2 (returns FALSE)
A5: A2gt;A1 (returns TRUE)
A6: A1gt;A3 (returns TRUE)

Does that help?

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

XL2002, WinXP-Proquot;Ron Coderrequot; wrote:

gt; Ummmm...Have you checked out the COUNT function.
gt; It counts numbers.
gt;
gt; The sum function will treat text cells as zeros so they don't needlessly
gt; corrupt the returned value.
gt;
gt; A1: User
gt; A2: =SUM(A1)
gt; returns 0
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;kaz ogonowskiquot; wrote:
gt;
gt; gt; While using Logical functions IF, I’ve found a problem that I wish to share
gt; gt; with Microsoft and other Excel users. Here is a very simple example.
gt; gt; Assuming that my data B1: B4 contains numbers and text. B1=2, B2=5, B3=User,
gt; gt; B4=7. The main task for formula B5: =SUM(IF(B1:B4gt;0,1)) is to count numbers.
gt; gt; The result of this formula is 4 but it should be 3. It is because
gt; gt; unfortunately this formula counts also text which is incorrect. The logical
gt; gt; test in this case is evaluated to true for numbers bigger than zero and
gt; gt; unfortunately also for any text in cells. As a consequence, the formula B5
gt; gt; counts texts the same way as numbers. To avoid this situation a logical test
gt; gt; with a condition gt; 0 for data which contains text should be evaluated to
gt; gt; false (not to true like it is done in Excel). Of course I can also easily
gt; gt; eliminate this kind of situation by putting an additional condition but the
gt; gt; point is to eliminate it overall the way I’ve suggested above. Actually
gt; gt; Function IF works that way if any text is assigned to a condition lt; 0 or
gt; gt; equal zero (Logical test in this case is evaluated to false). I think that
gt; gt; you’d agree with me that if logical test with condition for example User gt; 0
gt; gt; in the following formula ( the result of this formula is 2) =IF(quot;Userquot;gt;0,2)
gt; gt; is evaluated to true looks a little awkward and beyond that you can see
gt; gt; consequences of creating other formulas.
gt; gt; Thanks for your time.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; ----------------
gt; gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; gt; Agreequot; button in the message pane. If you do not see the button, follow this
gt; gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; gt; click quot;I Agreequot; in the message pane.
gt; gt;
gt; gt; www.microsoft.com/office/comm...et.f unctions

How about just using
=count(b1:b4)

It's restricted to just numbers.
kaz ogonowski wrote:
gt;
gt; While using Logical functions IF, I’ve found a problem that I wish to share
gt; with Microsoft and other Excel users. Here is a very simple example.
gt; Assuming that my data B1: B4 contains numbers and text. B1=2, B2=5, B3=User,
gt; B4=7. The main task for formula B5: =SUM(IF(B1:B4gt;0,1)) is to count numbers.
gt; The result of this formula is 4 but it should be 3. It is because
gt; unfortunately this formula counts also text which is incorrect. The logical
gt; test in this case is evaluated to true for numbers bigger than zero and
gt; unfortunately also for any text in cells. As a consequence, the formula B5
gt; counts texts the same way as numbers. To avoid this situation a logical test
gt; with a condition gt; 0 for data which contains text should be evaluated to
gt; false (not to true like it is done in Excel). Of course I can also easily
gt; eliminate this kind of situation by putting an additional condition but the
gt; point is to eliminate it overall the way I’ve suggested above. Actually
gt; Function IF works that way if any text is assigned to a condition lt; 0 or
gt; equal zero (Logical test in this case is evaluated to false). I think that
gt; you’d agree with me that if logical test with condition for example User gt; 0
gt; in the following formula ( the result of this formula is 2) =IF(quot;Userquot;gt;0,2)
gt; is evaluated to true looks a little awkward and beyond that you can see
gt; consequences of creating other formulas.
gt; Thanks for your time.
gt;
gt;
gt;
gt; ----------------
gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; Agreequot; button in the message pane. If you do not see the button, follow this
gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; click quot;I Agreequot; in the message pane.
gt;
gt; www.microsoft.com/office/comm...et.f unctions

--

Dave Peterson

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 quot;valuesquot; in the cells. Certainly one could
argue that since your use of the sum function is quot;ambiguousquot;
it might be reasonable that the word quot;userquot; should have a value greater
than zero, since the cell will clearly not contain the value of zero
if it contains the value quot;userquot;.=SUM(IF(ISNUMBER((B1:B4)),1,0)) (as an array)

Pieter Vandenbergkaz ogonowski gt; wrote:
: 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:B4gt;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 gt; 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 lt; 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 gt; 0
: in the following formula ( the result of this formula is 2) =IF(quot;Userquot;gt;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 quot;I
: Agreequot; 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 quot;I Agreequot; in the message pane.