Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# IF statement

Posted on 2014-12-23
Medium Priority
84 Views
Not sure if there are any EE's out there today if there are here is a conundrum for you please [Static Data is a Sheet Name]

I need the formula below to go in Cell G36

if G4 is blank, Blank

If Static Data A26=3 and F36 = Blank, Blank

but

If Static Data A26<>3, N/A

otherwise Blank
Many thanks
0
Question by:Jagwarman
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 8
• 4

LVL 85

Expert Comment

ID: 40514683
=IF(G4="","",if('Static Data'!A26=3,"","N/A"))

Note: your F36 test doesn't seem to be relevant based on your description?
0

LVL 24

Expert Comment

ID: 40514690
Rory, I think F36 fits here:

=IF(G4="","",if(and('Static Data'!A26=3,'Static Data'!F36=""),"","N/A"))

I'm assuming that, if A26=3 but F36<>"", then "N/A".
0

LVL 85

Expert Comment

ID: 40514697
I asked the question so we wouldn't have to assume... :)
0

Author Comment

ID: 40514793
hi Phillip, F36 is not in static Data it's in the current sheet so unfortunately it does not work
0

Author Comment

ID: 40514798
What I an trying to achieve is

today Is Tuesday so Static Data will show 3 in A26.

for the cell used yesterday it shows as blank because Static Data A26 =3

but I need the cell from yesterday to show as N/A not blank.

That is why the code needs to be
if G4 is blank, Blank

If Static Data A26=3 and F36 = Blank, Blank

but

If Static Data A26<>3, N/A

otherwise Blank
0

LVL 85

Expert Comment

ID: 40514803
That's just the same description. ;)

Basically your description says that if G4 is blank, you want blank, then if A26 <>3 you want N/A and for all other outcomes you want blank, so the test on F36 is irrelevant.
0

LVL 24

Expert Comment

ID: 40514804
So

=IF(or(G4="",'Static Data'!A26=3),"","N/A")

Well, Rory was right. F36 is not relevant, because:

If Static Data A26=3 and F36 = Blank, Blank
but If Static Data A26=3 and F36 is not Blank, then Blank (because it then goes to "Otherwise blank")
0

Author Comment

ID: 40514808
Hmm!

But

Today Static Data = 3

but

G4 is not = Blank

F36 is blank

so I need G36 to be N/A
0

LVL 24

Expert Comment

ID: 40514816
Doesn't make sense.

This is what you have said: But Today Static Data = 3 but G4 is not = Blank F36 is blank so I need G36 to be N/A

However, what you have previously said:

If G4 is blank, Blank. ELSE If Static Data A26=3 and F36 = Blank, Blank

It seems that it needs to be Blank, based on your previous logic. Please clarify.
0

Author Comment

ID: 40514834
I believe these two statements are the same:

if G4 is blank, Blank                                          [so if it's not blank and A26 <>3 then N/A]

If Static Data A26=3 and F36 = Blank, Blank

but

If Static Data A26<>3, N/A

and

Today Static Data = 3

but

G4 is not = Blank

F36 is blank

so I need G36 to be N/A
0

LVL 24

Expert Comment

ID: 40514837
In this example, are you saying Static Data A26 = 3 or not?
0

Author Comment

ID: 40514841
yes it does because 3 = Tuesday
0

LVL 24

Expert Comment

ID: 40514846
Then, let's go through the logic (as far as I can see it)

Condition 1: if G4 is blank, Blank - not true, so go to Condition 2

Condition 2: If Static Data A26=3 and F36 = Blank, Blank - not true, as F36 is not blank, so go to Condition 3

Condition 3: but If Static Data A26<>3, N/A - not true, as A26 = 3, so go to Condition 4.

Condition 4: otherwise Blank - Therefore, the answer is blank.

Otherwise, when would you ever get to condition 4?
0

Author Comment

ID: 40514857
today

Condition 2: is true

Condition 2: If Static Data A26=3 and F36 = Blank, Blank - TRUE as F36 is blank,
0

LVL 85

Expert Comment

ID: 40514867
F36 is still irrelevant due to your Else clause. The only time you don't return blank is if A26 <> 3.
0

LVL 24

Expert Comment

ID: 40514870
Condition 3 is FALSE - Static Data A26 <> 3, so you have to go to Condition 4, based on your logic.
0

Author Comment

ID: 40514877
ok yes condition 4
0

LVL 24

Expert Comment

ID: 40514885
So, the answer is Blank. So F36 (should that read F26?) is not relevant, because the result from Condition 2 and Condition 4 are the same.
0

Author Comment

ID: 40514894
I have just noticed my error, sorry

If Static Data A26=3 and F36 = Blank, Blank should read If Static Data A26=3 and F36 = Blank, N/A
0

LVL 24

Accepted Solution

Phillip Burton earned 2000 total points
ID: 40514911
So, here we go:

=IF(G4="","",if(and('Static Data'!A26=3,F36<>""),"","N/A"))
0

Author Closing Comment

ID: 40514917
Phillip, you are a star. Thanks for sticking with it. you can now go off and have yourself a nice G&T or whatever else you fancy. Have a great Christmas.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month7 days, 22 hours left to enroll