If AND? If OR?

[cyd000]

I have three columns of numbers


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]B1
[/TD]
[TD]C1
[/TD]
[/TR]
[TR]
[TD]1158.50
[/TD]
[TD]1562.50
[/TD]
[TD]1250.00
[/TD]
[/TR]
</tbody>[/TABLE]


I need a formula that looks at all three numbers and returns one of them based on criteria.

If A1 is bigger than B1 or C1, then B1 should be returned. If C1 is bigger than A1, then C1 should be returned. If A1 is bigger than C1 but smaller than B1 then A1 should be returned.

Nothing I am familiar with IF(AND, IF(OR, SUMIFS will work.

Any ideas?

 

[mikerickson]

Unless C1 is bigger than A1, the conditions resolve to "return the middle value"
=IF(C1>A1, C1, SUM(A1:C1)-MIN(A1:C1)-MAX(A1:C1))

 

[jtakw]

Hi,

I'm going to give it another shot, I "think" I got it right this time...maybe...

=IF(C1>A1,C1,IF(A1< B1,A1,B1))

 

[thisoldman]

Unless C1 is bigger than A1, the conditions resolve to "return the middle value"
=IF(C1>A1, C1, SUM(A1:C1)-MIN(A1:C1)-MAX(A1:C1))

If we always want the middle of three values,

=LARGE(A1:C1, 2)
​

Edit: Oops! forgot to finish
=IF(C1 > A1, C1, LARGE(A1:C1, 2))

 

[jtakw]

Unless C1 is bigger than A1, the conditions resolve to "return the middle value"

If we always want the middle of three values

Unfortunately, I don't believe it's always the middle value...

Using the formula in my post #12:

Excel 2010
	A	B	C	D	F
1	1	2	3		3
2	3	2	1		2
3	4	3	2		3
4	5	4	5		4
5	4	5	3		4
Sheet1

Cell Formulas
Range	Formula
F1	=IF(C1>A1,C1,IF(A1))

 

[cyd000]

Thank you all SO much!

I used =IF(A1>B1,IF(A1< C1,C1,B1),A1) from jtakw and it works great.

To answer the question about there being = values. I reviewed a couple of months worth of numbers and couldn't find an instance, I think it would be highly unlikely but I wonder if I should take that into account?

 

[jtakw]

Thank you all SO much!

I used =IF(A1>B1,IF(A1< C1,C1,B1),A1) from jtakw and it works great.

To answer the question about there being = values. I reviewed a couple of months worth of numbers and couldn't find an instance, I think it would be highly unlikely but I wonder if I should take that into account?

Hi cyd000,

Thanks for the feedback, unfortunately, my formula (from post #6) as you stated in the above post, gives incorrect result when A1 is smaller than B1 and C1.
I've compiled a table showing all the suggested formulas so far, the highlighted cells are where I believe the formula fails your logic in OP, so it seems the only working formula is the one from my post #12 (Column J formula in this sample highlighted Green).

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	1	2	3		3	3	1	3	3	3	3
2	3	2	1		2	2	2	2	2	2	2
3	4	3	5		5	3	5	3	5	5	5
4	5	4	5		5	4	4	4	5	4	5
5	4	5	3		4	5	4	5	4	4	4
6	4	4	5		5	5	4	5	5	5	5
7	4	4	3		4	4	4	4	4	4	4
8
9					Post #2	Post #3	Post #6	Post #8	Post #11	Post #12 & 14	Post #13
Sheet1

Cell Formulas
Range	Formula
E1	=IF(A1>C1,IF(A1>B1,B1,A1),MAX(A1,C1))
F1	=IF(OR(A1>B1,A1>C1),B1,IF(C1>A1,C1,IF(AND(A1>C1,A1),A1,"")))
G1	=IF(A1>B1,IF(A1< C1,C1,B1),A1)
H1	=IF(OR(A1>B1,A1>C1),B1,IF(C1>A1,C1,IF(OR(A1>C1,A1),A1,"")))
I1	=IF(C1>A1, C1, SUM(A1:C1)-MIN(A1:C1)-MAX(A1:C1))
J1	=IF(C1>A1,C1,IF(A1< B1,A1,B1))
K1	=IF(C1 > A1, C1, LARGE(A1:C1, 2))

Please review and confirm.