![]() |
nested if
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 |
nested if
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: 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 |
nested if
=IF(OR(A1:A4<0),"neg","pos") entered as an array formula (Control Shift
Enter). -- David Biddulph "Gary Wessle" wrote in message ... 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 |
nested if
On Jul 30, 2:42 pm, Gary Wessle wrote:
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 |
nested if
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:A 4))=-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. |
nested if
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. |
nested if
"Gary Wessle" wrote...
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. |
All times are GMT +1. The time now is 11:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com