Case statement using nested ifs

Jak7217

New Member
Joined
Jan 11, 2019
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Hey All!

I am running into an issue I can't figure out. I've got a big set of data and there is a specific column that I want to make a case statement based on the results.

If the field, lets call it A1, is...
=42
or =43
or is between 70 - 99, excluding 79 and 81
then I want to call it "Yes"

Can anyone help me with the syntax on this? I am struggling to get it right.

JAK7217
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You said:
big set of data and there is a specific column
did not say what column You just said A1
And what does this mean:
You said "Call it "Yes"

What does call it "Yes" mean

And for best answer provide specific details like you said:

You said:
=42
or =43
or is between 70 - 99, excluding 79 and 81

Is this exactly what you want or are you just giving examples
 
Upvote 0
removed F ()

BUT i'm sure more is involved in this

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
 
Upvote 0
Sorry for unclear wording. I just want to write a statement that if cell A1 meets any of the criteria below then the output of my if statement will say "Yes".

=42
or =43
or is between 70 - 99, excluding 79 and 81
 
Upvote 0
how about

=IF(A1="","",IF( OR( A1 =42, A1 = 43, AND(A1>=70, A1 <=99,A1<>79,A1<>81)), "yes", "no"))

Book3
AB
142yes
22no
36no
479no
53no
612no
781no
899yes
988yes
1070yes
11 
12 
Sheet1
Cell Formulas
RangeFormula
B1:B12B1=IF(A1="","",IF( OR( A1 =42, A1 = 43, AND(A1>=70, A1 <=99,A1<>79,A1<>81)), "yes", "no"))
 
Upvote 0
Solution
how about

=IF(A1="","",IF( OR( A1 =42, A1 = 43, AND(A1>=70, A1 <=99,A1<>79,A1<>81)), "yes", "no"))

Book3
AB
142yes
22no
36no
479no
53no
612no
781no
899yes
988yes
1070yes
11 
12 
Sheet1
Cell Formulas
RangeFormula
B1:B12B1=IF(A1="","",IF( OR( A1 =42, A1 = 43, AND(A1>=70, A1 <=99,A1<>79,A1<>81)), "yes", "no"))
This is exactly what I was looking for - thank you!
 
Upvote 0
Glad to see you received an answer. Your subject title says Case Statement which is used in Excel vba. So that made me think you wanted to use vba and use case
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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
Back
Top