Solved

IF statement

Posted on 2014-12-23
21
78 Views
Last Modified: 2014-12-23
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
Comment
Question by:Jagwarman
  • 9
  • 8
  • 4
21 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
=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

by:Phillip Burton
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I asked the question so we wouldn't have to assume... :)
0
 

Author Comment

by:Jagwarman
Comment Utility
hi Phillip, F36 is not in static Data it's in the current sheet so unfortunately it does not work
0
 

Author Comment

by:Jagwarman
Comment Utility
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

by:Rory Archibald
Comment Utility
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

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:Jagwarman
Comment Utility
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

by:Phillip Burton
Comment Utility
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

by:Jagwarman
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
In this example, are you saying Static Data A26 = 3 or not?
0
 

Author Comment

by:Jagwarman
Comment Utility
yes it does because 3 = Tuesday
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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

by:Jagwarman
Comment Utility
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

by:Rory Archibald
Comment Utility
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

by:Phillip Burton
Comment Utility
Condition 3 is FALSE - Static Data A26 <> 3, so you have to go to Condition 4, based on your logic.
0
 

Author Comment

by:Jagwarman
Comment Utility
ok yes condition 4
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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

by:Jagwarman
Comment Utility
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

by:
Phillip Burton earned 500 total points
Comment Utility
So, here we go:

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

Open in new window

0
 

Author Closing Comment

by:Jagwarman
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now