An IF Formula - Across 2 Columns and Return Value

[Dark_Knight_897]

Hi Guys,

I am having trouble devising a formula which looks at the results from 2 columns together and compares them against a particular criteria and then returns a response.

So basically: =IF(the term time in cell X = N and the submitted hours in cell Q are > 20, then return "Issue 1"; and if the term time in cell = Y and the hours in cell Z > 40, then return "Issue 2")

Rule 1: If its term time and hours are > 20 then = "Issue 1")
Rule 2: If its not term time and hours are > 40 then = "Issue 2")


Does this make any sense to anyone?

Ive attached a sample data if anyone can make sense of it??


DK

[Pepe Le Mokko]

Like this ?

=IF(AND(F5="N",E5>20),"issue1",IF(AND(F5="Y",E5>40),"issue2",""))

[Dark_Knight_897]

Hi Pepe,
That is brilliant, that works. How do you know how to structure the argument, where to put the brackets etc? I find this very confusing to build.
DK

[Glenn Kennedy]

Multiple failures and bitter experience... Great drivers to improving learning.

[Pepe Le Mokko]

Multiple failures and bitter experience... Great drivers to improving learning.

And of course reading Glenn's posts...

[Pepe Le Mokko]

Hi Pepe,
That is brilliant, that works. How do you know how to structure the argument, where to put the brackets etc? I find this very confusing to build.
DK

Just remember the syntax of the IF function
IF something happens, do "this"
if it does NOT do "that"
now the "that" could be another IF and you can nest lots of them if needed ( which can often be avoided by careful selection of functions and a good data layout, which is essential for easy analysis)
As for the brackets, they follow the same rules as basic arithmetic. If you open 10 brackets you have to close them.
When I have a long formula I count the open and closed brackets, and correct as needed

[Dark_Knight_897]

Hi Pepe,
Ok Ill need to look into this again. I get the prinical of IF this, THEN that. The practice of doing it and changing bit and pieces of the formula is still trickey to get right.
Ill reach out if any issues. I find without knowing the basics of the formulas like IFs and Pivots and other formulas, you cannot problem solve any issue given to you at work.
Any pointers on where to learn the basics of IF statement structures? There are so many rubbish things on internet that always want to charge.
Thanks again
DK