This is by far the very best tutorial video I have come across on UA-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.
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!)
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.
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!
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))
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.
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.
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.
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.
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!
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.
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
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 ?
Your videos are so very helpful and I've learned a ton! It's made my life so much easier. However, I've come across a question that I can't find a video for. Excel tells me I'm trying to list too many conditions in my formula. Would you be able to point me to a video to help or even better, help me fix the formula? Thanks so much. =IF(AND(AK18="F",AL18
It works fine if only the first third is used, but I want to be able to differentiate between the F, S, and T and then what to do depending on which of those three letters appears. Thanks.
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 ....
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%?
I wish I was hanging out with you at CUNY having fun with Excel : ) But, wait, we can hang out here at UA-cam!!! Thanks for the support with comments, buying shirts and such !!!! And remember, when you get out there and are working, you can always search the channel for whatever topic you need, Kevin!
Is it possible to IF OR (cell="value", cell="value) return "value" OR if cell is blank return "value" I don't know if that makes any sense.... These videos make me look really smart at work!! I appreciate it!
I want to know if I can use the nested "IF" ,"And","or" function to do the following": I'm making a electrical Load schedule that will calculate the totals for a electrician. I have in one column, three entries 1. Lights 2.Outlets 3. ACU If the user puts in the Lights or Outlets in (olumn A column C inputs in VA. My formula:=IF(OR(C6="LO",C6="CO"),"va") which works, but I want to also include the ACU so my result for ACU will be "hp" instead of VA, I can use a note on the sheet to tell the user to put Outlets and Lights first, but as Newton's law states... well you know.Plus the entries for Outlets and Lights may be short or long, there are alot more other "If" functions I'm using but they will depend on these being correct first. Plus I may have to use this same function using VA,hp and W further in the sheet.
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
Mike is the most humble Excel guru/genius in the world! Thank you, Mike!
You are OR and AND Welcome, Rodrigo : )
BEST EXCEL TUTORIALS ON THE PLANET!
This is by far the very best tutorial video I have come across on UA-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
I am glad that you like the videos!
I am glad that you like the videos! Thanks for liking the videos!
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!)
Thank you XLSFUN - your voice sounds a bit tired - please take care yourself - you are our angel !
I am glad that you like it! I will keep it up!
I am glad that you like the video!
not only My Favorite Channel but also the best for everyone
Thank you, Hazem!!!
Your explanation is very clear. I wouldn't mind paying to learn this from you.
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!
brilliant - thanks, you presentation was scaffolded step by step from simple to complex and your explanations were clear
You are welcome!
I am glad that you like it!
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...
I am glad that the videos help!
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 : )
Thank you. You are the BEST teacher I have ever listened to!
I am glad that you like it!!
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!
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!!!!
The best ever. Thank you so much, you are helping change the world one trick at a time.
Yes, I am aware of that!
You are welcome for the tutorials!
👍
Thanks! I am glad that the videos help!
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.
You are welcome!
Love logical formulas. Thanks Mike.
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 : )
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!
I always enjoy how you explain excel, it is really fun! ExcelIsFun!!!!
Many thanks! You explain more better than official Office online
You are genius ... rhanks a lot for teach us...pray for you sir
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 : )
Excellent explanations and not too hurried, even I could get it right! Keep on inspiring us dummies :)
it is very simple to understand...........Thank u sir
T SESHARAO You are welcome!
Great video!! Happy 4th of July. Got Don't Fear The Spreadsheet, quite a book.
Your work is excellence
thanks!! easy and direct to the tgt also for non English speakers
You are welcome!
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!
This tutorial is great. I am getting to it bit by bit. thanks a million.
Thanks!
Good sysmetical explanation. 👍
Glad you like it!, deynes!! Thanks for your support : )
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 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 : )
Your awesome Mike!!!
Glad to help, Anthony!!!
Your tutorials are amazing Mike. Being bad at Excel has hindered me from gaining grabbing good professional opportunities before. Not anymore :)
Best video ever.
Great tutorial! Thank you so much for all your videos. They are simply AWESOME!
Glad you like the video!
Really enjoyable - I feel much more confident with various functions. Thank you.
Simply great explanation.
great tutorial as always
THANK YOU SO MUCH YOU SAVED ME SO MANY HOURS!
Happy 4th!!
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))
Excellent, very understandable
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
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.
Brilliant tut!!
Glad you like it!
Very well presented many thanks:)
You are awesome!!!
Finally, like Yoda, A TRUE MASTER!!!
Thanks! This helped my understanding a lot.
Great explanation, thank you.
Thanks for the big help Sir.
You are welcome a lot, most awesome Delight In Life! Thanks for your consistent support : )
Thanks, Great Examples!
You are fabulous! ;-) I learn more from your site than any other!
Glad it helps!
Wow i think i have now grasp all the IF function(or (and 👍👍
Glad the video helped you with the IF, Dub Agent!!!
Your learning function(Trick) nice i like it
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 for this.
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.
i am your number one fan! thank you for this...;-)
Yes you can use conditional formatting.
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.
very good trainer
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!
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.
Thankyou sir...
Very nice and good explanation..
Thank you so much
Very useful and resourceful to me! Thx for this effort! I really learnt a lot :)))
thanks for the nice tutorial
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
Hey ExcelIsFun,
Did you know about the 'evaluate formula'-function in Excel. It could be very useful, also for in tutorials I think.
"BOOM!" Thanks for the help
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 ?
athsnks a lot and God bless u
excellent !!
I LIKE TOO MUCH YOUR VIDEOS SON !!!
CHEERS !!
WASSUP !!!!!!!!!!!!!
Your videos are so very helpful and I've learned a ton! It's made my life so much easier. However, I've come across a question that I can't find a video for. Excel tells me I'm trying to list too many conditions in my formula. Would you be able to point me to a video to help or even better, help me fix the formula? Thanks so much. =IF(AND(AK18="F",AL18
It works fine if only the first third is used, but I want to be able to differentiate between the F, S, and T and then what to do depending on which of those three letters appears. Thanks.
Me too!
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 ....
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
I your alright Excelisfun guy!!! 👍
I wish I was hanging out with you at CUNY having fun with Excel : ) But, wait, we can hang out here at UA-cam!!! Thanks for the support with comments, buying shirts and such !!!! And remember, when you get out there and are working, you can always search the channel for whatever topic you need, Kevin!
Is it possible to IF OR (cell="value", cell="value) return "value" OR if cell is blank return "value"
I don't know if that makes any sense....
These videos make me look really smart at work!! I appreciate it!
I'm getting better at this every day! The answer to my question is =IF(OR(cell="value",cell="value")"YAY!","")
"" is the code for blank cell
It is really very nice :-)
Ur the best. I got one file need to put benfit for target which is done. Could you please give me hand 🙏🌹
I want to know if I can use the nested "IF" ,"And","or" function to do the following": I'm making a electrical Load schedule that will calculate the totals for a electrician. I have in one column, three entries
1. Lights
2.Outlets
3. ACU
If the user puts in the Lights or Outlets in (olumn A column C inputs in VA.
My formula:=IF(OR(C6="LO",C6="CO"),"va")
which works, but I want to also include the ACU so my result for ACU will be "hp" instead of VA, I can use a note on the sheet to tell the user to put Outlets and Lights first, but as Newton's law states... well you know.Plus the entries for Outlets and Lights may be short or long, there are alot more other "If" functions I'm using but they will depend on these being correct first. Plus I may have to use this same function using VA,hp and W further in the sheet.
I love this
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
Nyce productive one thanks
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
Can i have one cell change into different colors based on what i type