IF OR AND Statements - EXCEL

mikes6058
mikes6058 used Ask the Experts™
on
I have the column below

Payment terms
60
45
30
35

I want to add a column with an if statement that will return new values based on the following conditions

If column "payment terms" contains values 45 or 30 or 35 then return 30 and if it contains 60 then return "58"

Mike
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Finance Analyst
Commented:
Assuming Payment Terms in column A:

=IF(OR(A1=45,A1=35,A1=30),30,58)

Thanks
Rob
Top Expert 2016
Commented:
Hi,

Maybe
=IF(OR(A1=45,A1=35,A1=30),30,IF(A1=60,58,""))

Open in new window

Regards

Author

Commented:
Thanks Rob,

How would it look if I added another value re below.

Payment terms
60
65
45
30
35

I want to add a column with an if statement that will return new values based on the following conditions

If column "payment terms" contains values 45 or 30 or 35 then return 30 and if it contains 60 or 65 then return "58"

Mike
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Top Expert 2016
Commented:
then try

=IF(OR(A1=45,A1=35,A1=30),30,IF(OR(A1=60,A1=65),58,""))

Open in new window

Rob HensonFinance Analyst
Commented:
With my formula, entries of 45, 30 or 35 will return 30, anything else will return 58.

I notice the values are incremental, is there a point at which the value of the entry will trigger the change, eg less than 60 returns 30, 60 or greater returns 58.

If you have multiple thresholds it may be easier to do it with a lookup table.

Author

Commented:
Thanks Rgonzo,

Do you know how I would write this formula using  a calculated column in a power pivot data model? I thought I would be able to use the same formula but I'm getting an error

Mike
Top Expert 2016
Commented:
if I remember well DAX's Or only accepts two arguments

so you could try something like this

=IF(A1<=45,30,IF(A1<=65,58,""))
or
=IF(A1<=45,30,58)

if you want multiple "Ors" use operator ||

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial