Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
Excel is counting incorrectly, I think. I am using counta to count
the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
On further examination, every counta, including those columns with
more than 1 X is counting an additional x that isn't there. On Feb 2, 12:19 am, rguerrie wrote: Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
You're using the wrong function for the task you want to achieve.
Try it like this: =COUNTIF(A1:A23,"x") COUNTA returns the total count of its arguments that *are not empty*. =COUNTA(A1:A23,"x") That formula contains 2 arguments: A1:A23 and the text string "x". If A1:A23 is completely empty the count from that range (argument) is 0. The second argument is the text string "x" which is "not an empty argument" so it counts as 1. So: =COUNTA(A1:A23,"x") = 1 =COUNTA(A1:A23,"x","y","z") = 3 Which is correct! -- Biff Microsoft Excel MVP "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
Yes, what you are doing wrong is not understanding the function you are
using. I would have hoped that as "a fairly sophisticated computer user" you might perhaps know how to use Excel's Help function to tell you what the COUNTA function does, before starting to complain about "bugs". You may perhaps have intended to use COUNTIF? -- David Biddulph "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
I will certainly switch to countif.
However, in the help page for COUNTA, the following example is given: =COUNTA(A1:A7,"Two") Counts the number of nonblank cells in the list above and the value "Two" It also describes counta as "Counts the number of cells that are not empty and the values within the list of arguments. " Perhaps I am not seeing something here? If counta(a1:a23) evaluates to zero, shouldn't counta(a1:a23,"x") (with or without the quotation marks--same results) also evaluate to zero? I appreciate your help. On Feb 2, 4:05 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: Yes, what you are doing wrong is not understanding the function you are using. I would have hoped that as "a fairly sophisticated computer user" you might perhaps know how to use Excel's Help function to tell you what the COUNTA function does, before starting to complain about "bugs". You may perhaps have intended to use COUNTIF? -- David Biddulph "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
Hmmm.
Can you give me an example of when counta(a1:a23,"x") might be used, then? On Feb 2, 1:19 am, "T. Valko" wrote: You're using the wrong function for the task you want to achieve. Try it like this: =COUNTIF(A1:A23,"x") COUNTA returns the total count of its arguments that *are not empty*. =COUNTA(A1:A23,"x") That formula contains 2 arguments: A1:A23 and the text string "x". If A1:A23 is completely empty the count from that range (argument) is 0. The second argument is the text string "x" which is "not an empty argument" so it counts as 1. So: =COUNTA(A1:A23,"x") = 1 =COUNTA(A1:A23,"x","y","z") = 3 Which is correct! -- Biff Microsoft Excel MVP "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
On Feb 2, 12:19 am, rguerrie wrote:
Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
Interestingly, this doesn't account for the cut and paste error.
Other people here have run into similar problems. I do appreciate your help. Anyway, I am relieved to see that counta was not the correct choice, even if that was not immediately obvious. On Feb 2, 4:05 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: Yes, what you are doing wrong is not understanding the function you are using. I would have hoped that as "a fairly sophisticated computer user" you might perhaps know how to use Excel's Help function to tell you what the COUNTA function does, before starting to complain about "bugs". You may perhaps have intended to use COUNTIF? -- David Biddulph "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
Hmmm.
Can you give me an example of when counta(a1:a23,"x") might be used, then? On Feb 2, 1:19 am, "T. Valko" wrote: You're using the wrong function for the task you want to achieve. Try it like this: =COUNTIF(A1:A23,"x") COUNTA returns the total count of its arguments that *are not empty*. =COUNTA(A1:A23,"x") That formula contains 2 arguments: A1:A23 and the text string "x". If A1:A23 is completely empty the count from that range (argument) is 0. The second argument is the text string "x" which is "not an empty argument" so it counts as 1. So: =COUNTA(A1:A23,"x") = 1 =COUNTA(A1:A23,"x","y","z") = 3 Which is correct! -- Biff Microsoft Excel MVP "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
PS, I am a fairly sophisticated computer user. However I am a fairly
naive Excel user. I am more than willing to admit that--which is why I asked for help, instead of just writing it off as a bug. On Feb 4, 9:39 am, rguerrie wrote: Interestingly, this doesn't account for the cut and paste error. Other people here have run into similar problems. I do appreciate your help. Anyway, I am relieved to see that counta was not the correct choice, even if that was not immediately obvious. On Feb 2, 4:05 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: Yes, what you are doing wrong is not understanding the function you are using. I would have hoped that as "a fairly sophisticated computer user" you might perhaps know how to use Excel's Help function to tell you what the COUNTA function does, before starting to complain about "bugs". You may perhaps have intended to use COUNTIF? -- David Biddulph "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
On Mon, 4 Feb 2008 06:21:45 -0800 (PST), rguerrie wrote:
Perhaps I am not seeing something here? If counta(a1:a23) evaluates to zero, shouldn't counta(a1:a23,"x") (with or without the quotation marks--same results) also evaluate to zero? No, because COUNTA is also evaluating "x" as a separate argument. For example, =COUNTA(A1:A23,x,y) will evaluate to 2, with nothing in A1:A23 --ron |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
I can't think of a practical situation where one would use that but you
might use something like this: =COUNTA(A1:A23,B1) -- Biff Microsoft Excel MVP "rguerrie" wrote in message ... Hmmm. Can you give me an example of when counta(a1:a23,"x") might be used, then? On Feb 2, 1:19 am, "T. Valko" wrote: You're using the wrong function for the task you want to achieve. Try it like this: =COUNTIF(A1:A23,"x") COUNTA returns the total count of its arguments that *are not empty*. =COUNTA(A1:A23,"x") That formula contains 2 arguments: A1:A23 and the text string "x". If A1:A23 is completely empty the count from that range (argument) is 0. The second argument is the text string "x" which is "not an empty argument" so it counts as 1. So: =COUNTA(A1:A23,"x") = 1 =COUNTA(A1:A23,"x","y","z") = 3 Which is correct! -- Biff Microsoft Excel MVP "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Bugs
In the example from the help page, there are 6 non-blank cells in the A1:A7
range, and adding the the value "Two" to the list of arguments brings the result up to 7 as given in the example. In your example, if COUNTA(A1:A23) comes to zero, COUNTA(A1:A23,"x") gives a total of 1 because you are adding COUNTA("x") which would be 1. Your list of arguments is (A1:A23,"x"), so you are counting the number of non-blank entries in that list, and that number is 1 as "x" is non-blank. -- David Biddulph "rguerrie" wrote in message ... I will certainly switch to countif. However, in the help page for COUNTA, the following example is given: =COUNTA(A1:A7,"Two") Counts the number of nonblank cells in the list above and the value "Two" It also describes counta as "Counts the number of cells that are not empty and the values within the list of arguments. " Perhaps I am not seeing something here? If counta(a1:a23) evaluates to zero, shouldn't counta(a1:a23,"x") (with or without the quotation marks--same results) also evaluate to zero? I appreciate your help. On Feb 2, 4:05 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: Yes, what you are doing wrong is not understanding the function you are using. I would have hoped that as "a fairly sophisticated computer user" you might perhaps know how to use Excel's Help function to tell you what the COUNTA function does, before starting to complain about "bugs". You may perhaps have intended to use COUNTIF? -- David Biddulph "rguerrie" wrote in message ... Excel is counting incorrectly, I think. I am using counta to count the number of Xs in a column. If I enter counta(a1:a23,"x") it evaluates to 1, which is incorrect. If I enter counta(a1:a23) it evaluates to zero, which is correct. To verify this I search/find a1 to a23 for x, it says there aren't any. I also visually inspect the column and there are no Xs. It does this in a number of columns. It also miscounts 1 X as 2 Xs. When there is more than 1 X in a column, it appears to count correctly. My spreadsheets aren't that big, less than a MB each. This is not the first problem I have encountered. When I copy then past special-- values only and transpose, it sticks in extra cells containing zeros. I get a lot of buggy stuff like this. Why do I not see other people complaining about things like this? I am a fairly sophisticated computer user. Can anyone tell me if I am doing something wrong. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink bugs | Excel Discussion (Misc queries) | |||
Two Bugs in Excel 2003 With Hyperlinks to Files | Excel Discussion (Misc queries) | |||
Excel 2007 Beta - 30 bugs | Excel Discussion (Misc queries) | |||
Fixing some bugs | Excel Worksheet Functions | |||
excel bugs | Excel Discussion (Misc queries) |