Count

  • Thread starter Thread starter Ravi
  • Start date Start date
R

Ravi

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
 
Ravi said:
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
Click to expand...

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.
 
I was given the following answer,=SUMPRODUCT(--(A1:A999>B1: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 said:
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
Click to expand...

One way (adjust range lenth to suit):
=SUMPRODUCT(--(A1:A999>B1:B999))
 
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
 
Mike,
the numbers given are very much in number format. I want to know what I
should mention in place of ''--'' [ ( SUMPRODUCT(--(A1:A999>B1:B999))].
 
In this formula, (A1:A999>B1: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:A9>B1: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:A9>B1:B9) and press F9 you
would expect to see
{FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}

Now highlight --(A1:A9>B1: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.
 
Back
Top