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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,311
Office Version
  1. 365
  2. 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
21,311
Office Version
  1. 365
  2. 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
7,245
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
21,311
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi BG
put a space before and after your "<" signs, as Mr Excel sees it as a HTML tag !!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,651
Messages
5,854,958
Members
431,689
Latest member
jacker01

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