IF OR AND Statements - EXCEL

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
mikes6058Asked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Assuming Payment Terms in column A:

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

Thanks
Rob
0
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

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

Open in new window

Regards
0
 
mikes6058Author 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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Rgonzo1971Connect With a Mentor Commented:
then try

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

Open in new window

0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
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.
0
 
mikes6058Author 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
0
 
Rgonzo1971Connect With a Mentor 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 ||
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.