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

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

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.

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

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

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

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