Test if range contains all positive or all negative numbers or a mix of both

[lovallee]

Hello,

I want to test if a range of cells contains all positive or all negative numbers or a mix of both.

I want to avoid using something long such as embedded IFs...

If there could be a simple formula, that would be great!

Thanks

 

[Scott T]

Try. Change A1:A7 to match your range

=IF(MIN(A1:A7)>0,"all pos",IF(MAX(A1:A7)<0,"all neg","mix"))

 

[lovallee]

Thanks Scott.
I got to that point but I was wondering if there was a way to avoid the embedded "IF"s...

 

[Scott T]

It is only two Ifs nested but you could use something like this

=VLOOKUP(SUMPRODUCT(((MIN(A1:A7)>0)*1)+((MAX(A1:A7)<0)*2)),{0,"mix";1,"all pos";2,"all neg"},2,0)

Also how should Zero be handled? both of the formulas will returned Mix if one of the numbers is zero.

 

[lovallee]

Thanks Scott.

Zeros would have been treated as positive in this case.

So far, it looks like the embedded ifs is the simplest solution!

For an instant, I thought that I could use some sort of mathematical or statistical Excel function as a proxy...(i.e. a function that returns a positive or negative or an error number when applied to inputs having different signs)... that would have been simple and handy!

 

[JackDanIce]

This works:

=LOOKUP(MATCH(COUNTIF(A1:A10,">0"),{10,5,0},-1),{1,2,3},{"All pos","mix","All neg"})

Where
10 = the count of all values in the range
5 = is any number within 0 and the count of all values in the range (used 5 as the medium number in this example)

Unfortunately, you cannot use COUNT(A1:A10) as an argument within the array to replace 10, similar for 5, but avoids lots of nested IFs if that's your preference..

Or you can write your own UDF e.g.

Public Function ReturnSignStatus(ByRef Rng As Range) As String

    Dim r   As Range
    Dim x   As Long
    
    x = Application.CountIf(Rng, ">0")
    
    Select Case x
        Case Is = 0
            ReturnSignStatus = "All neg"
        Case Is = Rng.Count
            ReturnSignStatus = "All pos"
        Case Else
            ReturnSignStatus = "Mix"
    End Select


End Function

 

[Scott Huish]

This will return -1 if any value in the range is negative. 1 if all the values are 0 or greater.
Confirm with CTRL-SHIFT-ENTER rather than just Enter.

=MIN(IF(A1:A10>=0,1,-1))