If AND? If OR?

[cyd000]

I have three columns of numbers

A1	B1	C1
1158.50	1562.50	1250.00

<tbody>
</tbody>

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?

 

[FDibbins]

Perhaps this?

=IF(A1>C1,If(A1>B1),B1,A1),max(A1,C1))

 

[Michael M]

MAybe

=IF(OR(A1>B1,A1>C1),B1,IF(C1>A1,C1,IF(AND(A1>C1,A1<B1),A1,"")))

 

[FDibbins]

Mike, I *think* your OR should be AND?

If A1 is bigger than B1 or C1

Or maybe I read it wrong lol

 

[Michael M]

Hi Ford
You could be right....
I was working on

A1 is bigger than B1 or C1

I guess we'll have to wait and see...

 

[jtakw]

Hi,

FDibbins formula had one too many ")", the corrected version is:

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

However, it would fail if A1 = C1, results in C1

Michael's formula didn't show completely, so here it is:

=IF(OR(A1>B1,A1>C1),B1,IF(C1>A1,C1,IF(AND(A1>C1,A1< B1),A1,"")))

However, it fails on if A1>C1 but < B1, results in B1

This is my suggestion:

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

 

[FDibbins]

Good catch, that was the remains of an AND() that I removed...
=IF(A1>C1,If(A1>B1,B1,A1),max(A1,C1))

There was no mention of equal values, although Im sure that could happen - the OP will need to clear that part up for us

 

[BG1992]

I have three columns of numbers

A1	B1	C1
1158.50	1562.50	1250.00

<tbody>
</tbody>

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?

I think this works (note the numbers were in row 2):

=IF(OR(A2>B2,A2>C2),B2,IF(C2>A2,C2,IF(OR(A2>C2,A2<B2),A1,"")))

 

[BG1992]

Oops, made a mistake, should have been:

=IF(OR(A2>B2,A2>C2),B2,IF(C2>A2,C2,IF(OR(A2>C2,A2<B2),A1,"")))

 

[Michael M]

Hi BG
put a space before and after your "<" signs, as Mr Excel sees it as a HTML tag !!