# If AND? If OR?

#### cyd000

##### New Member
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?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Perhaps this?

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

MAybe
Code:
``=IF(OR(A1>B1,A1>C1),B1,IF(C1>A1,C1,IF(AND(A1>C1,A1<B1),A1,"")))``

Mike, I *think* your OR should be AND?
If A1 is bigger than B1 or C1

Or maybe I read it wrong lol

Hi Ford
You could be right....
I was working on
Rich (BB code):
``A1 is bigger than B1 or C1``

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

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)

Last edited:
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

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,"")))

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,"")))

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

Replies
1
Views
72
Replies
4
Views
569
Replies
4
Views
701
Replies
7
Views
178
Replies
6
Views
199

1,203,073
Messages
6,053,379
Members
444,660
Latest member
Mingalsbe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back