If AND? If OR?

cyd000

New Member
Joined
Sep 22, 2015
Messages
9
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?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,728
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
MAybe
Code:
=IF(OR(A1>B1,A1>C1),B1,IF(C1>A1,C1,IF(AND(A1>C1,A1<B1),A1,"")))
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,728
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

I guess we'll have to wait and see...(y)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,383
Office Version
  1. 2016
Platform
  1. Windows
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:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

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

New Member
Joined
Dec 14, 2015
Messages
21
I have three columns of numbers


A1B1C1
1158.501562.501250.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

New Member
Joined
Dec 14, 2015
Messages
21
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,728
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Hi BG
put a space before and after your "<" signs, as Mr Excel sees it as a HTML tag !!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top