ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   nested if (https://www.excelbanter.com/excel-worksheet-functions/152360-nested-if.html)

Gary Wessle

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

Stefi

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


David Biddulph[_2_]

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




[email protected]

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


Roger Govier[_3_]

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.

MyVeryOwnSelf

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.

Harlan Grove[_2_]

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 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com