Excel Magic Trick 939: Use OR and AND Functions In IF Function For Logical Tests
ฝัง
- เผยแพร่เมื่อ 2 ก.ค. 2012
- Download Excel Start File: people.highline.edu/mgirvin/Y...
Download workbook: people.highline.edu/mgirvin/Ex...
1. IF Function: Put 1 of 2 things into a cell based on a logical test
2. AND Function: When all logical tests are passed, AND delivers a TRUE; If any one of the tests come out FALSE, AND delivers a FALSE
3. OR Function: When at least 1 logical tests is passed, OR delivers a TRUE; If any one of the tests come out TRUE, OR delivers a TRUE
Examples:
1) Example 1: IF function
2) Example 2: AND function with 2 logical tests inside IF function
3) Example 3: OR function with 2 logical tests inside IF function
4) Example 4: AND function with 3 logical tests inside IF function
5) Example 5: AND and OR Functions to create a "None" Logical Test
6) Example 6: AND and OR Functions to create a complex Logical Test
![เจอประจำ ตอน พักเที่ยง [EP.32]](http://i.ytimg.com/vi/jvqk6uJoVks/mqdefault.jpg)
Mike is the most humble Excel guru/genius in the world! Thank you, Mike!
You are OR and AND Welcome, Rodrigo : )
Once again you have proven that you are the MASTER!!!! I have learnt so much from you, more than I have ever learnt from a dumb course or MS Help (ha!)
This is by far the very best tutorial video I have come across on TH-cam!!! Your tutorial is so easy to follow and you explained everything to the T! Your video and your explanation are clearer than taking a course! Kudos.
+JR Castro Glad the video helped!
I can atest... he made it clear and fun....now Excel is my favourite in Microsoft
BEST EXCEL TUTORIALS ON THE PLANET!
I am glad that you like the videos! Thanks for liking the videos!
I am glad that you like it! I will keep it up!
I am glad that you like the videos!
I am glad that you like the video!
Thanks a lot! You really are a very good trainer! All your videos are truly awesome! Systematically you explain all options. Hat's off sir!!!
Mukim Morshed Glad you like the videos!
I am glad that you like it!
I am glad that you like it!!
Thank you so much!! You really made Excel fun and easy to learn! I used to avoid Excel and now I really love it. Explaining Excel functions are not an easy thing to do, so I appreciate the great help.
My friend, you do a simple superb job on teaching this in a way that regular people like me can understand! KUDOS and Thanks!
You are welcome!
Thank you. You are the BEST teacher I have ever listened to!
Fantastic tutorial. I'm somewhat fuzzy on the IF, AND, OR formulas but this is tutorial is easy to follow and well structured.
Are you still fuzzy after watching this? I hope the video helps!
@@excelisfun its cool that in every video i see, that u still react to new comments lol. Good customer support (:
@@ashafar8484 : )
Great video with clearly explained content! You really are an excellent trainer, I like how you vocalize the UI action / shortcut you are doing instead of just doing it silently. I always dragged to extend a formula, didn't know about double clicking to "send it down". I also learned the F4 trick from this video for locking cells. Nice job!
Thanks! I am glad that the videos help!
Yes, I am aware of that!
You are welcome for the tutorials!
👍
I always enjoy how you explain excel, it is really fun! ExcelIsFun!!!!
I am glad that the videos help!
You are welcome!
Excellent! I’ve really been struggling with IF AND OR and I think this explanation might have finally made it sink in for me! Thank you!
You are welcome, trina c!!!!
Keep it up !!! If u have a fan club, we all will become its members.. u r really systematic and planned.. Hats off to you Sir...
Thank you XLSFUN - your voice sounds a bit tired - please take care yourself - you are our angel !
not only My Favorite Channel but also the best for everyone
Thank you, Hazem!!!
brilliant - thanks, you presentation was scaffolded step by step from simple to complex and your explanations were clear
You are welcome!
Who else just did their homework in 2020 with the help of this oldie but goodie?!!!
Glad to help with your homework, NnekaBaby !!! Thanks for your support with your comments, thumbs ups and Sub : )
Your explanation is very clear. I wouldn't mind paying to learn this from you.
The best ever. Thank you so much, you are helping change the world one trick at a time.
Many thanks! You explain more better than official Office online
Really enjoyable - I feel much more confident with various functions. Thank you.
Many many thanks! This is your 3rd video I am watching. You saved the day! (by the way, nice voice and arthrosis. I mean, very clear.)
Thanks!
Your tutorials are amazing Mike. Being bad at Excel has hindered me from gaining grabbing good professional opportunities before. Not anymore :)
This tutorial is great. I am getting to it bit by bit. thanks a million.
Hey, I really appreciate your videos, I've learned a LOT from your lessons. I have one question though: Is there a video where you explain how torefer to an array of cells using the IF function? For example: =IF(AND(A1=B3,OR(A2=B4:B10)))- this one doesnt work obviously because IF doesnt "understand" the reference to an array.
Just found and subscribed to your channel! Your a excellent teacher and excel god! Thank You!
You are welcome, Nameless One!!! Thank you very much for your support with your comment, Sub and Thumbs Up : )
Thank you for this.
Love logical formulas. Thanks Mike.
Thanks! This helped my understanding a lot.
Excellent explanations and not too hurried, even I could get it right! Keep on inspiring us dummies :)
THANK YOU SO MUCH YOU SAVED ME SO MANY HOURS!
Thanks a lot! You really are a very good trainer! All your videos are truly awesome! Systematically you explain all options. I would like know can we make data base software for HRMS wherein we can by simply putting employee code it shows all the data like first nam, middle name, surname, designation, address, department, ctc, job profile, d.o.b, d.o.j, age in years, date of retirement like so.
Great tutorial! Thank you so much for all your videos. They are simply AWESOME!
Glad you like the video!
Simply great explanation.
Great explanation, thank you.
Happy 4th!!
"BOOM!" Thanks for the help
Finally, like Yoda, A TRUE MASTER!!!
Thanks, Great Examples!
Thanks to this video I was able to solve a question that I spent multiple hours on, in under 5 mins.
Awesome! I am glad that the video helps, Zainab! Thank you for the support with your comments, Thumbs Ups and Sub : )
Thank you so much sir. Step by step I tried with Excel sheet and everything worked fine. Nice explanation. I Easily learnt nesting of IF, AND, OR.
You are welcome!
You are genius ... rhanks a lot for teach us...pray for you sir
Brilliant tut!!
Very well presented many thanks:)
Very useful and resourceful to me! Thx for this effort! I really learnt a lot :)))
Thank you for the detailed information. I just have a question. I am needed to make an excel chart that includes a percentage in one column, number of sales in one column (minimum a person has to make is 44) and number of mistakes in a month. To sum this database into a final gradin rate, I would need to come up with a function (the if function seems to be the one in my opinion). My question to you is, which function(s?) should I be using and how can I integrate a data that is different than the other ( percentage-number)? Thank you very much in advance
I enjoy & love to watch yr video...explanation is very clear & straight forward !!I'm having some problems... by trying to insert the "Qty in stk" to the "Pending orders".What formula will best suit the following conditions ?1. "Product code" must be the same for these two files :"Pending Orders" & "Stk on hand".2. "Supplier id" must also be the same for these two files3. "Customer id" must be the same for these two files with additional conditions :a) If "Cust_id = PRO027" must specify clearly on result column as- Example : "PRO027=1,000" (to sum up the qty for PRO027 under "Stk on hand".b) Sometimes, per "Pending Order" line item may have two results ##:##1) Qty in stk (condition 1 to 3 are meet) &##2) "PRO027=1,000"4. When the result for above 1 to 3 is meet will need to 'sum' all the qty for repeated "product code/Cust/Sup" under the "Stk on hand" qty.Thank you in advance for your help.
def helpful... thanks! :)
Thankyou sir...
Very nice and good explanation..
I would like to combine these two IF functions in one cell - =IF(K134="M1-W1",J134,IF(K134="Monthly",J134,0)) and =IF(F4="Completed",0,IF(F4="PR Delivered to Logs",C4))
great tutorial as always
Great video!! Happy 4th of July. Got Don't Fear The Spreadsheet, quite a book.
Yes you can use conditional formatting.
Your work is excellence
i am your number one fan! thank you for this...;-)
AWESOME..........!!
thanks for the nice tutorial
You are fabulous! ;-) I learn more from your site than any other!
Glad it helps!
very good trainer
Good sysmetical explanation. 👍
Glad you like it!, deynes!! Thanks for your support : )
Awesome!
it is very simple to understand...........Thank u sir
T SESHARAO You are welcome!
Hi, thanks for your nice tutorial. i see in your tutorial you use $ sign in the function by shortcut key, pls teach me how to use $ sing in the formula by shortcut key. thanks!
Me too!
You are awesome!!!
Awsm videos...i wanna ask if there is any fucntion thru which i can map a field that the value enter in that colum should range between only 2 values or the value put should not b more than the defined value ?
thanks!! easy and direct to the tgt also for non English speakers
You are welcome!
Nyce productive one thanks
Your awesome Mike!!!
Glad to help, Anthony!!!
athsnks a lot and God bless u
Your video provides me solution to most excel issues I've had living up to its name "Excellisfun". I am having trouble getting one to work. I tried using a nested if and function for a large number of data but no success yet. The condition are.....Vendor list = COD if series starts with "A" (A12345) and Pline = XY, Vendor=DOM if series starts with "A" (A12345) and Pline is BL, Vendor = DMM if series starts with "K" ( K12345) and Pline is DL, CL (not XY or BL) and Vendor = BUD if series does not start with a letter (12345) and Pline is XY,BL,DL,CL and other. Since I am only interested with the first letter in the "series" column, I included a left function. The single formula works by itself, but when I have all three if functions nested, it gives a #Value error. Single formula is =if(and(left(series,1)="A",Pline="XY"),"COD","BUD". my nested if function is =if(and(left(series,1)="A",Pline=""XY"),"COD"),if(and(left(series,1)="A",Pline="BL"),DOM,if(and(left(series,1)="K",Pline"XY","BL", "DMM"),"BUD"). Please tell me, wher did it go wrong or is it completely wrong.
Thank you so much
I love this
Thanks for the big help Sir.
You are welcome a lot, most awesome Delight In Life! Thanks for your consistent support : )
HELLO ,is it possible to link the content of the box with number (10) in the "NEW FORMATING RULE" window at conditional formating >>format only top or bottom ranked values , with an external cell of the sheet ? Thanks ....
thank bro ;)
Wow i think i have now grasp all the IF function(or (and 👍👍
Glad the video helped you with the IF, Dub Agent!!!
your videos are so helpful, thank you. How would I write IF a range of cells = yes or n/a then another cell = yes, but if any = no then the cell = no. I could do each individually but wonder if there is a faster way.
Can we use Wildcard characters(*,?,~) with if with logical functions
Best video ever.
Your learning function(Trick) nice i like it
Hey ExcelIsFun,
Did you know about the 'evaluate formula'-function in Excel. It could be very useful, also for in tutorials I think.
i want to know a conditon if any cell value 15 then another which i selected is value is 1, and if 18 then 2 if 26 then 3, how can i make it
Excellent, very understandable
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
It is really very nice :-)
Hi and thanks for the comprehensive video. I`m trying to create something in relation to this plus conditional formatting so wondering whether you can help me a little. I have a sheet that tracks accidents that employees have. The criteria is -once i type their name it should turn into a yellow colour ( meaning first accident) , then second - amber and third - red. Now, the problem i`m finding is how to get Excel to check all the names above, match how many times their name has appeared on the sheet and return the correct result as in amber or red colouring of the cell. Thanks in advance
fantastic
excellent !!
I LIKE TOO MUCH YOUR VIDEOS SON !!!
CHEERS !!
WASSUP !!!!!!!!!!!!!
What function do I use to model a situation where a ratio of less than 20% will yield 6% in an empty cell, 9% if the ratio is in between 20% and 50%, and 15% if the ratio is greater than and equal to 50%?
Probably VLOOKUP. Here is a video all about VLOOKUP:
VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function