Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorrect Counting of IF function
While using Logical functions IF, Ive 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:B40,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 Ive 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 youd 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/comm...et.f unctions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorrect Counting of IF function
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 "kaz ogonowski" wrote: While using Logical functions IF, Ive 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:B40,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 Ive 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 youd 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/comm...et.f unctions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorrect Counting of IF function
How about:
=COUNT(B1:B4) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "kaz ogonowski" wrote in message ... While using Logical functions IF, Ive 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:B40,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 Ive 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 youd 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/comm...et.f unctions |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorrect Counting of IF function
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: A1A2 (returns FALSE) A5: A2A1 (returns TRUE) A6: A1A3 (returns TRUE) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: 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 "kaz ogonowski" wrote: While using Logical functions IF, Ive 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:B40,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 Ive 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 youd 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/comm...et.f unctions |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorrect Counting of IF function
How about just using
=count(b1:b4) It's restricted to just numbers. kaz ogonowski wrote: While using Logical functions IF, Ive 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:B40,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 Ive 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 youd 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/comm...et.f unctions -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorrect Counting of IF function
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 kaz ogonowski 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:B40,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incorrect result using NPER worksheet function | Excel Worksheet Functions | |||
Changing worksheet cells from within a function | Setting up and Configuration of Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |