Excel 2010 Nesting IF and AND Functions

In this video, Neil demonstrates how the AND and IF functions can complement one another to complete a larger task.

[yasmeenb]

Hi Neil! Thank you so much for the tutorial, it was definitely helpful! So my question is related to how to get data from 3 different columns to give a result in a single column.
My current example is this, one column has "fasting glucose", the next has, "random glucose" and the third has, "2 hours post-prandial" in my final and fourth column I would like it to read, "result". A patient cannot fulfil all 3 quota on one day so I thought that I should use the "OR" function? The only problem is that I have used a nested IF function to create the results that I would like. This is my current formula:
=IF(S2>11, 1;"high";IF(S2>=3, 9;"normal";"low")) -> random
However I need to add these 2 as well:
=IF(S2>6, 9;"high";IF(S2>=3, 9;"normal";"low")) -> fasting
=IF(S2>7, 8;"high";IF(S2>=3, 9;"normal";"low")) -> 2 hours post prandial

As you can see the results are different but the normal and low values are the same for all of them. I really have no clue where to start! Also please excuse the slightly different variation to my formula, I am using Apple "numbers" and not "Microsoft excel" so the, comma is replaced with ; semicolon.
Thank you!

[jibinkpious]

Hi, thanks for the video.. Can you show us an example where multiple (at least 5) conditions are tested..?

[rbtelee]

I have 3 values, say 6, 8 and 10. What would be a formula to find that middle value of 8?

[brianparkinson]

Neil, How about if I want to use more that one logical tests. I used =IF(AND($G$18>=I6, $G$20>J6), "Call SUBSCRIBER", "Don't call SUBSCRIBER") and it worked for one particular case, but if I wanted the results of 8 different tests returned to one cell?

[justinegalloway]

Can you create an If and function that looks for a number in a column and if that number is there and one other criteria is met it is true if not it is false

[amandacarlson]

Hi Neil -

This is a great tutorial, but the problem I'm having goes one step further and I'm hoping you can help:

Using the language from your example, what if the equation I'm trying to write tells us to Call the Patient, IF they are both overdue AND need a Procedure OR, say, need a Followup (and are still overdue)? I'm getting stuck here.

Thanks!

[MrLiabela]

Dear Neil, i need a help in if function, e.g if the value of A1=1670 or 2500 or 2000 then the answer should be zero but if the value of A1=0 then answer should be 1670 or 2500 or 2000. please kindly help me what type of function should i use to get the exact answer.

[donnaal]

How do I write the below function as one.=Sumif(date, ">12/31/16, Total)  and I need this in the same formula  =Sumif(Sales_person, AB3, Total).  The date and Salesperson are ranges that have been set up.

[arjayrepoyla]

Hi, i would like to ask for help on my formula. this is an example of my tabulation. Lets say I used column A1:A6.
A1= today()
A2= 25-jun-18
A3= 2-jul-18
A4= 9-jul-18
A5= 16-jul-18
A6= 23-jul-18
Suppose i will be using A7 to write my formula. I have three schedules to rotate every week; 6am, 2pm, 10pm and i wrote it this way but didn't work why?:
=if(A1>=A2, "6AM", IF(A1>=A3, "2PM", IF(A1>=A4, "10PM", "")))

[kartik]

Excel func for
Identify all employees who are male and have
exactly 4 years of post-secondary education. What
is the average salary of these employees?

[copaneco]

Hi, I tried to use If & AND,  the logical test is >=  but not accept the formula and shows me an error, could you please help me out to resolv, thanks

[kvnconroy]

Hi I was wondering could you help with my query. I tried to figure it out using your see below

                           b3           c3
Name               score      result    pts
Kevin conroy       y             y         5
Kev  

if score is y then 5 points BUT.if result only is y then 2 points
but if both score & result are y then 5 like skysports super 6

I can use the IF, AND function to get 5
if the result is y only I cannot get 2
Not sure if it can be done in the one column????
=IF(C3="y", 2), IF(AND(B4="y", C4="y"), 5)
This is what I thought haha
Thanks
kevin