Positive/Negative Columns part II

[codeman_not]

Ok, I will try to explain this better. I have four columns (A1:D1). Those columns will either have a "1" or "-1" in them. In column 5, I am trying to figure out a way to show me how many of the columns are postive or negative (or the net positve vs negative of the four columns) (IE: 1,1,1,1 - column 5 = 4 and -1,-1,-1,-1 - column 5 = -4 and finally 1,1,-1,-1 = 0). I tried just using SUM(A1:D1), and that works for all scenarios except when there are 3 positves or negatives. IE: 1,1,1,-1 returns 2, when I am looking for it to return 3 (for 3 net positive columns). The same thing for the inverse (-1,-1,-1,1 = -2). Anyone know of any other way to tackle this other than SUM?

 

[codeman_not]

Here is a complete example of what I am trying to do:

1,1,1,1 (@sum = 4), (desired = 4)
1,1,1,-1 (@sum = 2), (desired = 3)
1,1,-1,-1 (@sum = 0), (desired = 0)
-1,-1,1,1 (@sum = 0), (desired = 0)
-1,-1,-1,1 (@sum = -2),(desired = -3)
-1,-1,-1,-1, (@sum= -4), (desired = -4)

 

[mikerickson]

If -1,-1,-1,1 is -3 instead of -2

Why is 1,1,-1,-1 a 0 ? Shouldn't it be a -2

 

[Kelbo]

Assuming that your data is in columns A to D is this what you are looking for:

=IF(COUNTIF(A2:D2,1)>2,COUNTIF(A2:D2,1),IF(COUNTIF(A2:D2,-1)>2,-COUNTIF(A2:D2,-1),0))

Kelbo

 

[mikerickson]

Try
=MAX(COUNTIF(A1:D1,"<0"),COUNTIF(A1:D1,">0"))*SIGN(SUM(A1:D1))

 

[codeman_not]

Both of those worked - thank you very much guys. Much appreciated!

 

[mikerickson]

I still don't understand why -1, -1, 1, 1 is counted as 0 and -1, -1, -1, 1 is counted as -3.

One is comparing positive agianst negative, the other is counting only the negative.

 

[codeman_not]

Hi Mike. In regards to your question (why is 1,1,-1,-1 = 0), let me explain it this way - Let's say 1 or -1 represents a sales increase or decrease for a company over the last 4 quarters. I am trying to determine how many of those quarters were positive or negative. In the case of 1,1,1,-1 - three of the four quarters were positive - so thats what I want to show. With respect to "1,1,-1,-1", there were two positive and two negative quarters - the two offset each other and the net effect is a wash or 0. Hope that explains where I am coming. Thanks again for your help!