nested if

[Gary Wessle]

Hi

how can I write a function to check if 4 cells contain negative number
then it returns the word "neg" but if all cells are positive it return
"pos"?

thanks alot

 

[Guest]

It depends! E.g. if the four cells are in a contiguous range, then this is a
solution:

Function posneg(fourcells As Range) As String
Dim onecell As Range
retsign = "pos"
For Each onecell In fourcells
If onecell.Value < 0 Then
retsign = "neg"
Exit For
End If
Next onecell
posneg = retsign
End Function

Regards,
Stefi


„Gary Wessle†ezt írta:

 

[David Biddulph]

=IF(OR(A1:A4<0),"neg","pos") entered as an array formula (Control Shift
Enter).

 

[arogg]

Hi

how can I write a function to check if 4 cells contain negative number
then it returns the word "neg" but if all cells are positive it return
"pos"?

thanks alot

This formula will work even if the cells are not contiguous.

=IF(AND(A1>=0,B2>=0,C3>=0,D4>=0),"POS","NEG").

Just insert the names of the cells you want to test and you're in
business.

Allan Rogg

 

[Roger Govier]

Hi Gary

Since you say ALL negative or ALL positive, then the following array entered
formula (use Control + Shift + Enter) should do what you want

=IF(SUM(SIGN(A1:A4))=4,"Positive",IF(SUM(SIGN(A1:A4))=-4,"Negative","Mixed"))

Note it will give an answer of mixed, if any or all of the cells are blank.
The test can be refined to test for all blanks by checking for a result of
0.

 

[MyVeryOwnSelf]

how can I write a function to check if 4 cells contain negative number

then it returns the word "neg" but if all cells are positive it return
"pos"?

It isn't quite clear what's expected with mixed positives and negatives, or
with a zero, but maybe this'll help get started:

=IF(MAX(A1,A4,C1,C4)<0,"neg",IF(MIN(A1,A4,C1,C4)>0,"pos","neither"))

This assumes the four cells are A1,A4,C1,C4.

 

[Harlan Grove]

how can I write a function to check if 4 cells contain negative number
then it returns the word "neg" but if all cells are positive it return
"pos"?

If all 4 cells are in a single range, e.g., C3:C6, you could use formulas
like

=IF(COUNTIF(C3:C6,"<0")=4,"neg",IF(COUNTIF(C3:C6,">0")=4,"pos","other"))

If the 4 cells aren't in a single range, you could use FREQUENCY.

=IF(INDEX(FREQUENCY((C3,D5,E7,F9),{-1E-300;0}),1)=4,"neg",
IF(INDEX(FREQUENCY((C3,D5,E7,F9),{0}),2)=4,"pos","other"))

Neither need to be entered as array formulas.