IF statement

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
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Note: your F36 test doesn't seem to be relevant based on your description?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Rory, I think F36 fits here:

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

Open in new window


I'm assuming that, if A26=3 but F36<>"", then "N/A".
0
Rory ArchibaldCommented:
I asked the question so we wouldn't have to assume... :)
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

JagwarmanAuthor Commented:
hi Phillip, F36 is not in static Data it's in the current sheet so unfortunately it does not work
0
JagwarmanAuthor Commented:
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
Rory ArchibaldCommented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
So

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

Open in new window


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
JagwarmanAuthor Commented:
Hmm!

But

Today Static Data = 3

but

G4 is not = Blank

F36 is blank

so I need G36 to be N/A
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
JagwarmanAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
In this example, are you saying Static Data A26 = 3 or not?
0
JagwarmanAuthor Commented:
yes it does because 3 = Tuesday
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
JagwarmanAuthor Commented:
today

Condition 2: is true

Condition 2: If Static Data A26=3 and F36 = Blank, Blank - TRUE as F36 is blank,
0
Rory ArchibaldCommented:
F36 is still irrelevant due to your Else clause. The only time you don't return blank is if A26 <> 3.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Condition 3 is FALSE - Static Data A26 <> 3, so you have to go to Condition 4, based on your logic.
0
JagwarmanAuthor Commented:
ok yes condition 4
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
JagwarmanAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
So, here we go:

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.