Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Formula result incorrect
I'm a beginner to using Excel formulae and have run across a puzzling
problem. Why does my attempt to COUNT the number of entries in column e that are equal to 5098 when the date is 5/27 give me an incorrect result? I get "1" but can see that there are at least 2. Here is a subset of the data: a b c d e DATE TIME TRUNK ANI DNIS 5/27/2005 7:32:12 121 5414 5/27/2005 7:32:14 122 5098 5/27/2005 7:32:17 123 5098 Here is the formula: =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Formula result incorrect
Maybe double check your data types. If data in column A is not text, use
datevalue to convert "5/27/05" to a number (you should read excel help regarding dates - they are actually stored as numbers). Also, if column E is numeric, remove the quotes. If col E is text, leave the quotes. =COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500)) "Andrew Chalk" wrote: I'm a beginner to using Excel formulae and have run across a puzzling problem. Why does my attempt to COUNT the number of entries in column e that are equal to 5098 when the date is 5/27 give me an incorrect result? I get "1" but can see that there are at least 2. Here is a subset of the data: a b c d e DATE TIME TRUNK ANI DNIS 5/27/2005 7:32:12 121 5414 5/27/2005 7:32:14 122 5098 5/27/2005 7:32:17 123 5098 Here is the formula: =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Formula result incorrect
The formula that you suggested is an *array* formula. -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. A non-array alternative: =SUMPRODUCT((A2:A500=DATEVALUE("5/27/05"))*(E2:E500=5098)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMB" wrote in message ... Maybe double check your data types. If data in column A is not text, use datevalue to convert "5/27/05" to a number (you should read excel help regarding dates - they are actually stored as numbers). Also, if column E is numeric, remove the quotes. If col E is text, leave the quotes. =COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500)) "Andrew Chalk" wrote: I'm a beginner to using Excel formulae and have run across a puzzling problem. Why does my attempt to COUNT the number of entries in column e that are equal to 5098 when the date is 5/27 give me an incorrect result? I get "1" but can see that there are at least 2. Here is a subset of the data: a b c d e DATE TIME TRUNK ANI DNIS 5/27/2005 7:32:12 121 5414 5/27/2005 7:32:14 122 5098 5/27/2005 7:32:17 123 5098 Here is the formula: =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks. |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Formula result incorrect
You hit the nail on the head. The date column was a date, not text. However
it required ragdyers SUMPRODUCT implementation to work correctly. Thanks, Andrew "JMB" wrote in message ... Maybe double check your data types. If data in column A is not text, use datevalue to convert "5/27/05" to a number (you should read excel help regarding dates - they are actually stored as numbers). Also, if column E is numeric, remove the quotes. If col E is text, leave the quotes. =COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500)) "Andrew Chalk" wrote: I'm a beginner to using Excel formulae and have run across a puzzling problem. Why does my attempt to COUNT the number of entries in column e that are equal to 5098 when the date is 5/27 give me an incorrect result? I get "1" but can see that there are at least 2. Here is a subset of the data: a b c d e DATE TIME TRUNK ANI DNIS 5/27/2005 7:32:12 121 5414 5/27/2005 7:32:14 122 5098 5/27/2005 7:32:17 123 5098 Here is the formula: =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Formula result incorrect
Yes, it appears I did leave that out. thanks!
"Ragdyer" wrote: The formula that you suggested is an *array* formula. -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. A non-array alternative: =SUMPRODUCT((A2:A500=DATEVALUE("5/27/05"))*(E2:E500=5098)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMB" wrote in message ... Maybe double check your data types. If data in column A is not text, use datevalue to convert "5/27/05" to a number (you should read excel help regarding dates - they are actually stored as numbers). Also, if column E is numeric, remove the quotes. If col E is text, leave the quotes. =COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500)) "Andrew Chalk" wrote: I'm a beginner to using Excel formulae and have run across a puzzling problem. Why does my attempt to COUNT the number of entries in column e that are equal to 5098 when the date is 5/27 give me an incorrect result? I get "1" but can see that there are at least 2. Here is a subset of the data: a b c d e DATE TIME TRUNK ANI DNIS 5/27/2005 7:32:12 121 5414 5/27/2005 7:32:14 122 5098 5/27/2005 7:32:17 123 5098 Here is the formula: =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks. |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Formula result incorrect
I already answered this I think in a thread titled:
" What does this mean?" I believe the array formula he was entering MAY have resolved to COUNT(FALSE) which returns 1 no matter what. Does not change the validity of other suggestions here and pardon me if I am wrong anyway. "Ragdyer" wrote in message ... The formula that you suggested is an *array* formula. -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. A non-array alternative: =SUMPRODUCT((A2:A500=DATEVALUE("5/27/05"))*(E2:E500=5098)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JMB" wrote in message ... Maybe double check your data types. If data in column A is not text, use datevalue to convert "5/27/05" to a number (you should read excel help regarding dates - they are actually stored as numbers). Also, if column E is numeric, remove the quotes. If col E is text, leave the quotes. =COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500)) "Andrew Chalk" wrote: I'm a beginner to using Excel formulae and have run across a puzzling problem. Why does my attempt to COUNT the number of entries in column e that are equal to 5098 when the date is 5/27 give me an incorrect result? I get "1" but can see that there are at least 2. Here is a subset of the data: a b c d e DATE TIME TRUNK ANI DNIS 5/27/2005 7:32:12 121 5414 5/27/2005 7:32:14 122 5098 5/27/2005 7:32:17 123 5098 Here is the formula: =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks. |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Formula result incorrect
as ragdyer pointed out, i omitted the part about the count function being an
array formula, which requires CSE (CNTRL+SHIFT+ENTER). "Andrew Chalk" wrote: You hit the nail on the head. The date column was a date, not text. However it required ragdyers SUMPRODUCT implementation to work correctly. Thanks, Andrew "JMB" wrote in message ... Maybe double check your data types. If data in column A is not text, use datevalue to convert "5/27/05" to a number (you should read excel help regarding dates - they are actually stored as numbers). Also, if column E is numeric, remove the quotes. If col E is text, leave the quotes. =COUNT(IF((A2:A500=DATEVALUE("5/27/2005"))*(E2:E500=5098),E2:E500)) "Andrew Chalk" wrote: I'm a beginner to using Excel formulae and have run across a puzzling problem. Why does my attempt to COUNT the number of entries in column e that are equal to 5098 when the date is 5/27 give me an incorrect result? I get "1" but can see that there are at least 2. Here is a subset of the data: a b c d e DATE TIME TRUNK ANI DNIS 5/27/2005 7:32:12 121 5414 5/27/2005 7:32:14 122 5098 5/27/2005 7:32:17 123 5098 Here is the formula: =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incorrect result using AVEDEV formula | Excel Discussion (Misc queries) | |||
incorrect formula result in 2003 | Excel Discussion (Misc queries) | |||
Incorrect result for simple SUM formula | Excel Discussion (Misc queries) | |||
Median result used in formula gives incorrect result | Excel Worksheet Functions | |||
formula result incorrect | Excel Discussion (Misc queries) |