Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
Earlier I have asked a question whether we can find the count of number of
times the value increased Col A when compared to Col B in a given range, the answer given did not work |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
Hi,
The answer provided looks fine to me, what unexpected result did you get? Mike "Ravi" wrote: Earlier I have asked a question whether we can find the count of number of times the value increased Col A when compared to Col B in a given range, the answer given did not work |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
"Ravi" wrote in message
... Earlier I have asked a question whether we can find the count of number of times the value increased Col A when compared to Col B in a given range, the answer given did not work So why have you started a new thread, rather than replying in the previous one and quoting sufficient text for anyone to pick up? We have no idea what answer you were given. Also, what do you mean by "did not work"? Did you get an error result, or an incorrect count, or what? I for one cannot understand what you mean by "the value increased Col A when compared to Col B in a given range". Try to explain what you want, perhaps with a simple example, and you are much more likely to get a helpful response. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
I was given the following answer,=SUMPRODUCT(--(A1:A999B1:B999)) , for the
question, and for this I have got the answer as zero, please find the example below. Anyhow I didn't understood the two ' -' mentioned in the answer. A B 78 791 98 1384 357978 1534 31 31857 751 316 364987 3165 35 357 323 157 47 31 Ravi" wrote in message ... I am trying to count how many times the value in Col A has exceeded the value in Col B in a given range, How to get this One way (adjust range lenth to suit): =SUMPRODUCT(--(A1:A999B1:B999)) "Ravi" wrote: Earlier I have asked a question whether we can find the count of number of times the value increased Col A when compared to Col B in a given range, the answer given did not work |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
Ravi,
The formula as given to you works fine and returns 5 for this set of data. If you aren't getting that answer then I suspect its because your numbers aren't really numbers and are probably text. Put this in a cell =ISNUMBER(A1) It will return TRUE if A1 actually contains a number. If it soesn't then you must investigate why your numbers are text. Mike "Ravi" wrote: I was given the following answer,=SUMPRODUCT(--(A1:A999B1:B999)) , for the question, and for this I have got the answer as zero, please find the example below. Anyhow I didn't understood the two ' -' mentioned in the answer. A B 78 791 98 1384 357978 1534 31 31857 751 316 364987 3165 35 357 323 157 47 31 Ravi" wrote in message ... I am trying to count how many times the value in Col A has exceeded the value in Col B in a given range, How to get this One way (adjust range lenth to suit): =SUMPRODUCT(--(A1:A999B1:B999)) "Ravi" wrote: Earlier I have asked a question whether we can find the count of number of times the value increased Col A when compared to Col B in a given range, the answer given did not work |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
Mike,
the numbers given are very much in number format. I want to know what I should mention in place of ''--'' [ ( SUMPRODUCT(--(A1:A999B1:B999))]. "Mike H" wrote: Ravi, The formula as given to you works fine and returns 5 for this set of data. If you aren't getting that answer then I suspect its because your numbers aren't really numbers and are probably text. Put this in a cell =ISNUMBER(A1) It will return TRUE if A1 actually contains a number. If it soesn't then you must investigate why your numbers are text. Mike "Ravi" wrote: I was given the following answer,=SUMPRODUCT(--(A1:A999B1:B999)) , for the question, and for this I have got the answer as zero, please find the example below. Anyhow I didn't understood the two ' -' mentioned in the answer. A B 78 791 98 1384 357978 1534 31 31857 751 316 364987 3165 35 357 323 157 47 31 Ravi" wrote in message ... I am trying to count how many times the value in Col A has exceeded the value in Col B in a given range, How to get this One way (adjust range lenth to suit): =SUMPRODUCT(--(A1:A999B1:B999)) "Ravi" wrote: Earlier I have asked a question whether we can find the count of number of times the value increased Col A when compared to Col B in a given range, the answer given did not work |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count
In this formula, (A1:A999B1:B999) will return an array of 999 values, each
of which is TRUE or FALSE. Each minus sign simply multiplies these values by -1 (hence the two together effectively multiply by 1) which coerces TRUE to be 1 and FALSE to be 0. This is necessary as SUMPRODUCT only works with numbers. The formula works fine for me. As a way of looking into the problem, I suggest temporarily changing the ranges to something much smaller, for example =SUMPRODUCT(--(A1:A9B1:B9)) If you highlight part of a formula in the formula bar and press F9, you see the result of that part of the formula. (Press ESC to get out of this mode, as pressing ENTER would put the result permanently into the formula!) So, using your example data, if you highlight (A1:A9B1:B9) and press F9 you would expect to see {FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE} Now highlight --(A1:A9B1:B9) and press F9 and you would expect to see {0;0;1;0;1;1;0;1;1} This technique will help you to understand what the formula is doing and may well lead you to see what is wrong. "Ravi" wrote in message ... I was given the following answer,=SUMPRODUCT(--(A1:A999B1:B999)) , for the question, and for this I have got the answer as zero, please find the example below. Anyhow I didn't understood the two ' -' mentioned in the answer. A B 78 791 98 1384 357978 1534 31 31857 751 316 364987 3165 35 357 323 157 47 31 Ravi" wrote in message ... I am trying to count how many times the value in Col A has exceeded the value in Col B in a given range, How to get this One way (adjust range lenth to suit): =SUMPRODUCT(--(A1:A999B1:B999)) "Ravi" wrote: Earlier I have asked a question whether we can find the count of number of times the value increased Col A when compared to Col B in a given range, the answer given did not work |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |