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.
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!
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 ?
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))
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.
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.
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
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.
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.
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.
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 ....
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 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.
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
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!
Help, I'm trying to making a sheet that has column A is a drop down list, in column B, I would like it to say if column A is (answer 2) they will need Form 1, can you help?
I wish I was hanging out with you at CUNY having fun with Excel : ) But, wait, we can hang out here at TH-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!
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%?
Hi thanks for sharing your great knowledge.I do need a solution in excel like i do have some sort of prices of home appliances some are then below 100 and some are above hundred.I would like to add 5$ if the price will be above hundred $.Can you help me out ?
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.
Mike is the most humble Excel guru/genius in the world! Thank you, Mike!
You are OR and AND Welcome, Rodrigo : )
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!
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!)
I am glad that you like the videos!
Thank you XLSFUN - your voice sounds a bit tired - please take care yourself - you are our angel !
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!!!!
I am glad that you like the videos! Thanks for liking the videos!
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!
not only My Favorite Channel but also the best for everyone
Thank you, Hazem!!!
I am glad that you like the video!
I am glad that you like it! I will keep it up!
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...
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 : )
Your explanation is very clear. I wouldn't mind paying to learn this from you.
Thank you. You are the BEST teacher I have ever listened to!
I am glad that you like it!
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
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 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.
I am glad that you like it!!
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!
Yes, I am aware of that!
You are welcome for the tutorials!
👍
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 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!
Love logical formulas. Thanks Mike.
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 : )
I am glad that the videos help!
I always enjoy how you explain excel, it is really fun! ExcelIsFun!!!!
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 tutorials are amazing Mike. Being bad at Excel has hindered me from gaining grabbing good professional opportunities before. Not anymore :)
Excellent explanations and not too hurried, even I could get it right! Keep on inspiring us dummies :)
You are genius ... rhanks a lot for teach us...pray for you sir
You are welcome!
Thanks! I am glad that the videos help!
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.)
This tutorial is great. I am getting to it bit by bit. thanks a million.
thanks!! easy and direct to the tgt also for non English speakers
You are welcome!
it is very simple to understand...........Thank u sir
T SESHARAO You are welcome!
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 ?
Really enjoyable - I feel much more confident with various functions. Thank you.
Great tutorial! Thank you so much for all your videos. They are simply AWESOME!
Glad you like the video!
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))
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.
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.
Good sysmetical explanation. 👍
Glad you like it!, deynes!! Thanks for your support : )
Your work is excellence
Yes you can use conditional formatting.
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
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.
Great video!! Happy 4th of July. Got Don't Fear The Spreadsheet, quite a book.
Simply great explanation.
Finally, like Yoda, A TRUE MASTER!!!
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.
Thank you for this.
Thanks! This helped my understanding a lot.
Hey ExcelIsFun,
Did you know about the 'evaluate formula'-function in Excel. It could be very useful, also for in tutorials I think.
Great explanation, thank you.
Happy 4th!!
THANK YOU SO MUCH YOU SAVED ME SO MANY HOURS!
Best video ever.
Wow i think i have now grasp all the IF function(or (and 👍👍
Glad the video helped you with the IF, Dub Agent!!!
great tutorial as always
Your awesome Mike!!!
Glad to help, Anthony!!!
Thanks, Great Examples!
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.
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 ....
You are fabulous! ;-) I learn more from your site than any other!
Glad it helps!
Thanks!
Brilliant tut!!
Glad you like it!
Very well presented many thanks:)
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!
Thanks for the big help Sir.
You are welcome a lot, most awesome Delight In Life! Thanks for your consistent support : )
You are awesome!!!
Thankyou sir...
Very nice and good explanation..
Ur the best. I got one file need to put benfit for target which is done. Could you please give me hand 🙏🌹
very good trainer
Excellent, very understandable
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
Very useful and resourceful to me! Thx for this effort! I really learnt a lot :)))
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.
Your learning function(Trick) nice i like it
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
Good evening, I have a spreadsheet that has a formula that I cannot work out. Am I able to send it to you to ask for your assistance?
"BOOM!" Thanks for the help
Thank you so much
thanks for the nice tutorial
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
Help, I'm trying to making a sheet that has column A is a drop down list, in column B, I would like it to say if column A is (answer 2) they will need Form 1, can you help?
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 TH-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!
Thanks dad
i am your number one fan! thank you for this...;-)
It is really very nice :-)
athsnks a lot and God bless u
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
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
Hi thanks for sharing your great knowledge.I do need a solution in excel like i do have some sort of prices of home appliances some are then below 100 and some are above hundred.I would like to add 5$ if the price will be above hundred $.Can you help me out ?
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.
excellent !!
I LIKE TOO MUCH YOUR VIDEOS SON !!!
CHEERS !!
WASSUP !!!!!!!!!!!!!