Counting negative values

[Maciej Grzywna]

Hi All,

I'd be very grateful if someone could help me with this.
I have a following table:

No, Company,Var1
12, ZXC, 6
12, ZXC, 4
12, ZXC, -3
34, AAC, 2
34, AAC, 4
56, BBR, -5
78, CCV, -4
78, CCV, -12

What I need is another column with some variable that would turn e.g. 1 when
company has at least one row with negative value of Var1 and 0 if not. In
other words, i'd like to get:

No, Company,Var1, Var2
12, ZXC, 6, 1
12, ZXC, 4, 1
12, ZXC, -3, 1
34, AAC, 2, 0
34, AAC, 4, 0
56, BBR, -5, 1
78, CCV, -4, 1
78, CCV, -12, 1

I have to add that I have about 9000 rows and i'm using excel 2003.

Thanks
Maciek

 

[Peo Sjoblom]

If that's the case why do you get 1 for the first 2 values in your example?

=-1*(C2<0)

where C2 would be the first Var1

copy down


--


Regards,


Peo Sjoblom

 

[Maciej Grzywna]

Hi Peo,

in my example first 3 values concern the same company, and that's the case.
When at least one value "within" the company is negative then I want all
three rows to have 1 in the fourth column.
I hope that will help.

Thanks
Maciek

 

[Rick Rothstein \(MVP - VB\)]

Give this formula a try...

=--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0)

Rick

 

[Rick Rothstein \(MVP - VB\)]

Actually, use this equation instead (it suppresses the 0 if there is no
number in column C)...

=IF(C1="","",--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0))

And, of course, you can change the ranges as required by your data (but note
that you can copy the formula down passed your last piece of data to account
for future entries).

Rick

 

[Maciej Grzywna]

Rick

Thank you very much for your help.

Maciek

Actually, use this equation instead (it suppresses the 0 if there is no
number in column C)...

=IF(C1="","",--(SUMPRODUCT((C1:C100<0)*(B1:B100=B1))>0))

And, of course, you can change the ranges as required by your data (but
note that you can copy the formula down passed your last piece of data to
account for future entries).

Rick

 

[RAGdyer]

Rick really meant to include the absolutes:

=IF(C1="","",--(SUMPRODUCT(($C$1:$C$100<0)*($B$1:$B$100=B1))>0))

 

[Rick Rothstein \(MVP - VB\)]

Yes, you are right... I did forget about that; although the formula would
still work as I posted it provided there were no gaps in the data and the
data wasn't more than half the number of cells in a column. Of course, the
absolute ranges are more practical. I would point out, however, that the
columns do not really need to be absolute as the formulas are being copied
down (it doesn't hurt to make them absolute, but it isn't mandatory). Also,
because the first cell in the range is being compared to itself, the first
row of the range does not really need to be absolute either. Had I realized
my mistake in leaving out the absolute ranges, this is how I would have
posted the formula...

=IF(C1="","",--(SUMPRODUCT((C1:C$100<0)*(B1:B$100=B1))>0))

But, of course, your posted correction would work fine also.

With all that said, thank you for catching my omission and alerting me to
it... I really appreciate that.

Rick

 

[RAGdyer]

Ahhh ... Yes ... BUT ... It's so much more easier to simply hit <F4> once in
each relevant reference!<bg>

 

[Rick Rothstein \(MVP - VB\)]

Well, if you are going to be using F4 while typing the formula in (rather
than going back to use it to edit the ranges afterwards), then there is no
"extra" work involved... simply don't hit F4 for the first cell reference
and do so twice on the second cell reference of each range... same two key
presses of F4 per range, just saved up for use on one instead of both cell
references within it.<vbg>

Rick