Combining "if", "Large" and "small" in the same formula

[greg m]

I can not seem to get the formula correct for the following scenario. If any number in column a, rows 1-3 is greater than zero, return the largest number. However, if the numbers are all less than zero, return the smallest number. I tried =IF(A1:A3>0,LARGE(A1:A3,1),SMALL(A1:A3,1)). I continue to get an invalid formula. Any suggestions? Thank you for your help. Greg--

 

[Momentman]

Here's one way

Excel 2010
	A	B
1	-5	-56
2	-2
3	-56
Sheet3

Cell Formulas
Range	Formula
B1	=IF(COUNTIF(A1:A3,">0")>=1,MAX(A1:A3),IF(COUNTIF(A1:A3,"<0")=3,MIN(A1:A3),""))

 

[greg m]

Thank you for your response. If the data is as follows, will this formula still work?
A B
1 0 5
2 -5 8
3 0 -2
4 -5 8
The formula results are in row 4 columns A and B: In column A, if all numbers are all less than zero, it returned the smallest number (-5). In Column B any number is greater than or equal to zero it returned the largest number (8). I would like the same formula to satisfy each criteria. Does that make sense? Thank you,

 

[Momentman]

Let me understand, how are you getting -5 'cos all numbers in column A are not less than zero, only one is

 

[greg m]

True...my mistake. If all the numbers are less than or equal to 'zero' then I want to return the "smallest" number (this would be zero or any negative number). If that criteria is not met, then there is a positive number in the list and I would return the "largest" positive number. I think that defines the formula better. Thank you,

 

[Momentman]

This should work

Excel 2010
	A	B
1	0	5
2	-5	8
3	0	-2
4	-5	8
Sheet4

Cell Formulas
Range	Formula
A4	=IF(COUNTIF(A1:A3,">0")>=1,MAX(A1:A3),IF(COUNTIF(A1:A3,"<=0")=3,MIN(A1:A3),""))
B4	=IF(COUNTIF(B1:B3,">0")>=1,MAX(B1:B3),IF(COUNTIF(B1:B3,"<=0")=3,MIN(B1:B3),""))

 

[greg m]

Thank you...it works perfectly!