Excel Basics 13: IF Function Made Easy! IS Functions Too! 14 Epic Examples!!
ฝัง
- เผยแพร่เมื่อ 30 ก.ย. 2024
- Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: people.highlin...
Download Full Written Notes: people.highlin...
Class web page link: people.highlin...
Topics in this video:
1. (00:01) Detailed Introduction to the IF Function & Comparative Operators and Logical Tests
2. (08:53) IF Example #01: Your Bonus: =IF(C6 Greater Than =C11,C10,0)
3. (13:39) IF Example #02: In Balance?: =IF(B22=C22,"In Balance","NOT In Balance")
4. (17:16) IF Example #03: Bonus? TRUE or FALSE?: =C4 Greater Than =$F$4
5. (17:54) IF Example #04: Bonus? "Bonus" or "No Bonus": =IF(C11 Greater Than =$F$11,"Bonus","No Bonus")
6. (18:54) IF Example #05: Bonus? 0 or $750: =IF(C18 Greater Than =$F$18,$G$18,0)
7. (20:20) IF Example #06: Bonus Commission? 5% or 1%: =IF(C25 Greater Than $F$25,$G$25,$H$25)
8. (23:34) IF Example #07: Calculate Commission Paid: =IF(C32 Greater Than $F$32,$G$32,$H$32)*C32
9. (27:46) IF Example #08: "Under" or "Over": =IF(C39 Less Than $F$39,$H$39,$G$39)
10. (31:25) IF Example #09: "No Balance" or Show Nothing: =IF(C46=0,$H$46,"")
11. (35:03) IF Example #10: Is it a Number?: =ISNUMBER(C7)
12. (38:26) IF Example #11: Is cell empty?: =ISBLANK(C7)
13. (39:09) IF Example #12: Is it Text?: =ISTEXT(C7)
14. (40:23) IF Example #13: Checkbook Balance: =IF(ISBLANK(B5),"",G4-F5+E5)
15. (48:22) IF Example #14: Sales Category: =IF(L7 Greater Than $O$7,"Large","Small") and then make a PivotTable
16. (52:50) Summary - Conclusion
![[Live] ศึกมหึมันส์มวยไทยสังเวียนเดือด เวทีมวยชั่วคราวสวนสยาม | จันทร์ 30 กันยายน 2567](http://i.ytimg.com/vi/pM1k6f7JXUc/mqdefault.jpg)
I just finished a semester of an Excel class and I learned more from these videos in two days than I did in that whole semester. Thank you so much for this incredible content!
Yes, I am sorry about your semester. But I had a semester like you did 20 years ago, and that it is what inspired me to become a teacher of Excel and post videos at TH-cam : )
Now whenever I type a formula in excel there’s a voice in my head ‘equals sign!’ Haha ;)
Excel is fun, right ; )
The same here:-)
I just discovered a greatest content on excel. i learned so much.
I was busy and it took me several sessions to complete this video. The contents are great and easy to understand. I would like to thank you for this useful sharing and tips.
You are welcome!!!
Your explanation is so good, I am able to do the examples myself and then watch you do it!
You are welcome! Thanks for your support with your comment, Thumbs Up and Sub : )
Many educators charging to teach Excel on TH-cam and other platforms but I haven't seen this level of teaching with every minute details on this Channel. I have always been fan of your teachings.
People learning Excel today should Subscribe this channel.
Thank You for doing this hard work for us.
That has been my goal for 15 years here at TH-cam: make the best Excel Classes for all in the world and do it for free : ) : )
This is how I spend my Saturday morning, enjoying your videos, practicing along and learning lots of cool and useful stuff! Much appreciated! Thank you so much!
You are welcome, klarawijs!!!!
Hats off to you, really helps me a lot in my work.
Glad to help! Thanks for the hats off : )
I can see the emormous amount of efforts in these excel videos! Greatly appreciated!
Thanks for teh kind words! I am glad that you enjoy the videos, Sun K!!!
Ur a true skilled excel teacher i like ur teaching style so very understandable n to the point explaination....very learned n skilled man....👏👏👏👏👏👏👏🙌🙌🙌🙌🙌🙌🙌🙌
Thanks for another great and well explained video Mr Mike.
You are welcome! Thank you for your support : )
Thank you very much. During this quarantine time in my country, I managed to start this Excel Basic course from the beginning. Until Lesson 13, I have managed to improve my current knowledge multiple times. Love the details and effort that you have put in this series. Not to mention easy to understand example and the homework too. Again, thanks a lot Mr Mike !!
You are welcome very much. Glad to help, Faziani! Thanks for your support on each video that you learn from with your comment, thumbs up, and of course your SUB : )
บบ
This is the best Excel training TH-cam video ever posted...... Good work...
Thanks, Bassey! Glad you like it and thanks for your support with your comment, thumbs up and of course your Sub : )
Totally agree! The best training course on the net!
@@deadspace49186 , Thank you : )
This series is awesome man, thanks so much. You deserve over 1m subs, congrats!
Glad the videos help!!!
Awesome videos great Job. You are simply the best at what you do learning a lot from you
I am very happy that you are learning a lot from the classes that I post, William!!!!!
this might be my favorite video/function yet
IF is fun and powerful!!
Truly appreciate for your enormous effort in providing us free education for EXCEL! The videos are very well organised and easy to navigate, and the content is very well taught! Thank you !!!!!
Thank you. I knew about the if functions but I learned about the is functions for the very first time today.
That is IF and IS awesome : )
@@excelisfun yeah IF and IS :)
Thanku sir i am doing one video daily learning so much from u
I am glad that I can help!
I love so much your examples and how you always give a context to explain the formulas! I've never understood the utility of =right and =left until now! Thank you so much, you are the best!
You are welcome so much, Nathalia : ) Good examples is a really important part of the stories I tell to help us all fall in love with the power and efficiency of Excel. Thanks for your great support with your comments, and thumbs ups : )
Excellent video ❤
Hello everyone!
Question regarding this example: 24:37 Showing the decimal places cell D34 is actually 299.99 not 300 and since we are talking about money shouldn't the correct number to be shown 299.99 not 300?
Also, could you also have done it using this formula? =IF(B25>30000,ROUND(B25*0.05,2),ROUND(B25*0.01,2)) (disregard the actual cells my spreadsheet is different)
Hi Michael, I am unable to download the Excel file nor the PDF file. I am getting a 403 Forbidden error message.
I am not able to download the resources. Is there any issue in the linked documents and spreadsheets?
Can you plz fix it, I am missing out on those valuable hw.
P.S: you are the best excel teacher.
Really thanks for your explanation. I have a question on your last example. Why under the list of Country code, with sorting from A-->Z, MAR is on the top of the list?
You are amazing thanks for this content
You are welcome!
Enjoyed it, specially the checkbook example. :)
Hello, in Ex9, isn't that account balance=0 means Show Nothing rather than "No Balance"?
in example one, is it the same thing if i simply put =IF(C7,C10,0)?
Great, thanks! Easy to understand!
Glad this helps!!!
Today, I've learnt an amazing explanation of if and is function , you are great teacher ❤️
I am glad that you learned some powerful IF formula things! Thank you for your kind words : )
@33.30 if anybody wondering if COUNTA function will count the cells with zero length text string ... YES it does. Thanks!
Yes indeed!!!!
Great as usual , thanks .
Thank you : )
You're the Best!
48
simply amazing!
IF("aamir"="StudyExcelisfun","AamirHasExcelPowerAndFun","")
Thank you for your priceless help with excel. Much appreciated.
You are welcome for the video and files, pauline : ) : ) There is a price, though. I charge a comment and a thumbs up ; ) Thanks for your payment!!!!
Thank you !
Watched a lot and learnt a lot, really appreciate your videos.
I never skip the ad :)
I am glad that the videos help! : ) And, Thanks for the support, Tingting Shi!!!!
You sound a lot like Ross from friends..
At 40:28, in the checkbook form table, are your "Add" Subtract" columns the reverse of accounting "Debit" "Credit" columns? And don't you add Credits before subtracting Debits?
First, when you are only adding and subtracting, the adding and subtracting can be done in any order and you will always get the same answer. Second, in accounting Debit means Left and Credit Means Right. Period. Whether or not a Debit or Credit is a plus or minus depends on what type of account it is. In this example, since the account is an asset, if we were using a Debit/Credit accounting system, Debit would a plus and credit would be a minus. But this example is not using a Debit/Credit accounting system. Hopefully that makes sense to you : ) And hopefully you liked the video?
Can I prepare for MO-200 certification exam using this playlist? These are great lectures by the way.
No. I absolutely do not teach to that test. I teach how people use Excel in the working world. Sorry about that.
I need help from excel masters. My formula produces logical error. A1 cell value =9 and in another cell there is a formula like =IF(A1>10, A1=5, B2="Hi") How can I write formula like this without vba?
Tanq for giving so many examples & HATS OFF to the Hybrid Formula used for Cheque Book. :)
Awesome video, thanks!
Please how do I get the assignments ??
Thank you!
IF("Kizo says thank you", "You are welcome!!!","")
in the sheet HW(1-3) the text in cell F1 reads backwards. Either that or I am going mad...
You are not going made, you are remaining smart. There was an error in the file. I fix it and re-posted. Hopefully you can help support this full free class with comments and thumbs up on each video that you watch. BTW, thanks for helping me to fix this error. It is a Team effort to bring free and fun education to the world!
Great video (as are all your videos, Mike), but I'm wondering why at 52:30, MAR sorts at the top of an obviously alphabetical list.
I have investigated and can not figure it out. BUT, when I recreate it, everything sorts correctly, which means there is a hidden character or something else has occurred to corrupt it. This sort of strange behavior does occur sometimes and even though we may not have the detective skills to go back in time and figure it out, we know that something did happen to alter this particular data set... : (
I agree. Sometimes things just go funny. That's very hard to explain to students and a source of frustration for them. Thanks for checking into it.
I noticed that too Carol. I've seen it happen on another of Mikes examples, but can't remember now which one it was. I've tried looking into this issue, but not had any luck with a solution so far. It's certainly an oddity.
UPDATE: Actually, it might have something to do with custom sorts. It must think that MAR is March and sorting that to the top as an order of preference perhaps?
This is for an older version, but if you do a manual sort after the steps shown in the link below, it seems to fix it. Not ideal though.
contexturesblog.com/archives/2011/09/30/excel-pivot-table-sorting-problems/
Alan
A beautiful video, IF FUNCTION was never easy before for me , you made it a piece of cake. Really, enjoying like a harry potter book. Thanks and regards.
Thanks, @excelisfun for this beautiful video. Please let me know which playlist should I watch after I complete this playlist.
th-cam.com/play/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw.html
But you will have 12 videos let in this class...
ExcelIsFun yes I was just curious to know that's why I asked.
@@deveshdangi7209 : )
Sir,
Your every video is more than excellent. I am so confident that there is no other advanced tutorial and trainer like you. I am not interested to follow any other trainer's tutorial if you have tutorial in which I am interested to learn. If it is possible, I will give a lot of Thumbs Up in every of your videos. We need trainer like you to make the world more better. I usually suggest to download and follow your videos whoever want to learn excel meeting with me. Thanks a lot to keep love and patience in your teaching field.
Thought I was good at IF statements till I got to ex 7. Learned a great shortcut instead of =IF(C32>$F$32,(F32*$G$32),(F32*$H$32).
Awesome! I am glad that the video helps! Thank you for the support with your comment, Thumbs Up and Sub : )
thank you so much for your videos! Learning more about excel and data analysis indeed make me finish my job in a more efficient manner. Thank you!
Hi Mike, Thanks for your great work. I did No.8(Ex 7) =IF(C32>=$F$32,C32*$G$32,C32*$H$32) , is this also ok? The result was the same but the method is slightly different. Which would be better in terms of in real financial work. Thanks again.
Thank you for supporting the channel with a Thumbs Up and small comment on each video, and a Sub too : ) It is a small but important payment you can make for the free education : )
Yes, your is just about the same in terms of calculating, because the one I showed: =IF(C32>$F$32,$G$32,$H$32)*C32, and yours both have to run the test and perform the multiplication. The only advantage to the one I showed is that it is shorter and takes less time to create.
Pls sir, make more videos for us.. They're awesome. Thanks
Here are all 25 Excel Basics videos for you, Ugochukwu: th-cam.com/video/7rLSZYiz7qE/w-d-xo.html
Thanks!
You are welcome, S & P!!!!!! It is really S & P (Super and Perfect) that you make a donation - it really helps : )
Thanks!
You are welcome, Yew!!!! I am happy to see you progressing through the whole class. Do you try all the video examples and homework examples also?
God Bless you sir for this.
You are welcome, VIRTUE!!! Thanks for the support with your comment, Thumbs Up and Sub : )
You sir are a legend. Thanks for everything!
Great! Thumbs up!
Thanks, Teammate!
I have column X with certain cells that contain "no value" but but contains a formula that includes a zero length text string =IFERROR(U3+V3+W3,"") . If a cell in column X returns with "no value" and I have column Y that uses the IFS formula =IFS($X3>0.39,"3- High",$X3>=0.241,"2- Medium",$X3
Example 9 and 13 show how to use "" to return a zero length text string.
@@excelisfun sorry, I'm not explaining it clearly. Column X formula is =IFERROR(U3+V3+W3,"") and column Y formula is =IFS($X4>=0.4,"3- High",$X4>=0.249,"2- Medium",$X4
@@Sarah120486 try adding test , result: $X4="" , ""
My mind exploded right about 45:00 then.
Sorry about the explosion.
Brainfreeze!! Brainfreeze!
Just to add: in all of your formulas up to now (in the Basic course), you have put the last parenthesis " )" to finish the formula, whereas we can just Ctrl-Enter without inputting the last parenthesis and Excel will accept that. What do you think?
Yes, many short formulas do not require the close parenthesis : )
You also commented: Great video as usual! IF & IS functions are awesome! Thanks a lot for your great lessons and explanations! And ready homework sheets are a great bonus for us! The only question is: in your last example 14, you create an additional cell for "250" to use as the "hurdle" cell. Can't we just type "250" in numbers into the IF formula since this number "250" is not going to change?
The answer is: yes, if it will never change, then maybe. But then you still loose the accuracy of comprehension because the hurdle is not listed and labels on the face of the spreadsheet. The 250 is buried in some formula and people do not know what the hurdle is. If you think about typing 24 for 24 hours into formula (later time - earlier time)*24, we do not need to put it in cell because it will not change and we don't really need to see it in the spreadsheet either ...
Thanku very much i am really grateful to u for ur videos
Glad you like them!
Have you created any tutorials on MS Word?
Here is my full Office class: th-cam.com/play/PLrRPvpgDmw0l45snFj8uDmuDUMQeBAVtd.html
amazing every single time..."INSANE" detailing ...what an exceptional Teacher !!! ..
Glad it all helps, Apples Oranges : )
Super!!
Thumbs up
Thank you for the super thumbs up : )
always great Sir
Mike, just wanna let you know the homework in 1-3, your concatenation formula 3 outputs “extra charge” when customers use < the hurdle and “under” when they go over! Sounds like a certain telco we all know. 😅
Thanks for helping me to edit. I fixed it and posted it back put to the Team! The problem for me, of course, is that it takes about 1 1,000 hours to make this class and I have no editors... so I depend on Teammates like you to point out the mistakes - then I can fix and the Team is better : )
Love the usefulness of understanding number formatting! Thank you for explaining the IF and IS functions so clearly :)
Number Formatting is key to have fun in working with Excel. That IS true ; )
The way you teach is phenomenal, thanks for such a wonderful effort.
btw, I want to know why are we using the IS function while making the checkbook register because same results can be achieved by using the IF function only.
For the checkbook formula, there are at least 5 different ways to do it, each depends on the exact logic of how the checkbook is filled out. What are the exact mechanics of how a checkbook entry is mad.
Thumbs Up! Great usage of IS & IF function together and a great example of it (y) Thank youuuu!
Thanks for your support, Mostafa!!!
I have question mike, please I started to do analysis report with power bi desktop now, I do them with excel and I have a lot of fun working with excel, but I like to hair from you where do you find or for which things you find power bi better than excel ( I hope you understand my question because of my poor English)
Power BI is good because: the visualizations are interactive and it is a free tool and you can publish reports online. Excel is good because it can do anything: Excel Spreadsheet Formulas, PivotTables, Sheets with cells (not limited to tables like Power BI), Power Query, DAX and Power Pivot.
I made a formula for 41:30 myself. It is "=IF(ISNUMBER(F5), G4-F5, E5+G4)"
Awesome! Your invention is good : )
Mr Girvin you are simply spectacular. You make excel looks soooo easy. HATS OFF
Hopefully the making Excel look easy, will help you to become awesome with Excel, Akshay! Thanks for your support with your comment, Thumbs Up and Sub : )
+
: )
Is excellent the way that you make this classes from beginner level to advanced level an each one. Thank you Mr. Excel Teacher!
You are welcome, Victor! Thanks for your support with comments and thumbs ups : )
I needed an excel refresher and yours is the best. I love your teaching style and appreciate the work you have put into the homework sheets and training information. It has helped tremendously to actually have a way to practice. Thank you. I look forward to the next 6 lessons.
You are welcome! Please help support with your comment, Thumbs Up and Sub : )
Rad! Great video as always!
Thanks for the rad wishes, Anderson 40th!!!!! Rad is the highest praise that there is, so I appreciate : )
Every time when I watch your video my knowledge in Excell getting better and better. Thank you very, very much for your outstanding effort. You are the best teacher from whom I learned Excell.
Yes, for the people who want to put the time in to learn, this Excel Basics Course will lift you up and help you to become amazing with Excel : ) Thanks for your consistent support on each video that you watch and learn from, Shahin!!!!
This playlist has taken me longer than expected, but I am learning to be more efficient with each playlist. Keep going if you are novice like me.
Yes!!! That is great advice : ) Thanks, Lewis : )
I owe you all that I have learned from you, and I can only pray for you that God will increase you knowledge for the benefit of the people and pray for you always for good.
I am glad that the videos help, saeed!!! You can show the love by always click thumbs up and leaving a comment when you learn ; )
Hi Michael
Thank you so much for your all videos. I am greatly appreciated your teachings.
You are welcome, Aung!!! I am very appreciative of your support with your comments, Thumbs Ups and Sub : )
I don't need to pay courses just to learn excel because you had already great excel tutorials. keep it up the amazing job.
You are right!!!! My goal for 11 years here at TH-cam has been to provide free Excel Education. But you do have to pay with a comment and thumbs up on each video ; )
thank you Mr. Mike the video was useful and i appreciated it i'm sorry for watching it late because i was a little busy in my work
No matter when you watch it, I thank you and thank you for your support!
It's very helpful how you've included the title sheet on these workbooks to show what you'll be teaching in this lesson. Some things I already know, so it makes it faster to get to the stuff I need to learn. Thanks.
You are welcome! Be sure to support this free service with a Thumbs Up and small comment on each video : )
In IF(1) Ex1 I put : =IF(C7=TRUE,C10,0), is also valid?
That is a valid formula : )
@@excelisfun Ok thanks, great videos btw.
@@daries Thanks for your support on each video : )
Great video! When I use the ISNUMBER function, I get -1 (instead of TRUE) and 0 (instead of FALSE). Why is that, and how can I change it to TRUE/FALSE?
You might have a lead minus sign
That was it, thank you - couldn't even see it nested in right next to the = sign! Great videos, keep it up!
Is good from time to time return and watch the basics, getting always something new, because when we practice with the experience
we figure out that the mistakes sometimes are related to the basics(like what I figure out here with the IF function golden rule ), good job Mike good job!!!!!!
There is no doubt that this series of Basic Videos is not a typical Basic Series because I cover almost everything in a logical order. And as a user if we have all these basic details in our head in the correct order, then there is almost nothing we can not do with Excel : )
Great vid, I'm alway eager to see your new vids for over 6 years, it help me too much, thanks you very much
Glad the videos help! Thank you for your support with your comments and Thumbs Up : )
Do you cover the other IS functions in the advanced series? In any case, great video.
Yes. The next series after this one is the Highline Excel 2016 Class series.
Awam great video,
Glad it is great for you, Jigar!!!!
This is so cool, thank you! There are so many things you can do with these functions.
IF("You want to do something", "You can","You still can")
Oooh your formula was super smart too! (talking about 41:30)
Thanks, Yashverdhan!!!!
the checkbook balance formula was so smart i can't hahaha Good job man!
Glad you liked it!
Thanks
very good, i appreciate it
#13 and still going, bavaji!!!!!!
Very informative video
Glad they help!!!
Again and again thank you.
You are welcome again and again : )