Highline Excel 2016 Class 02: Excel Fundamentals: Comprehensive Formula Lesson (15 Examples)
ฝัง
- เผยแพร่เมื่อ 30 ก.ย. 2024
- Download Files: people.highlin...
In this video learn about:
1. (00:13) Introduction
2. (10:09) Excel’s Golden Rule
3. (04:31) Look at list of Formula Types and Formula Elements
4. (06:29) Number Formula Examples
5. (08:24) Text and Number Formulas for counting numbers above a hurdle
6. (11:15) When to combine Comparative Operator and Number in a cell or when to combine Comparative Operator and Number in the formula.
7. (12:36) DOLLAR function.
8. (14:09) How to combine Comparative Operator and Number in the formula.
9. (16:53) VLOOKUP function and Array Constant
10. (22:31) IF Function
11. (25:09) Logical Formula
12. (27:06) Aggregate Calculation / Aggregate Formula
13. (28:12) Array Calculation / Array Formula
14. (32:40) Excel Formula Order of Precedence (Excel’s Order of Operations)
15. (37:05) Tracking Down Errors in Excel: 4 Step Method.
16. (40:37) Types of Errors
17. (46:19) When to use ROUND function
18. (49:43) 3 ways to enter cell references into formulas
19. (53:22) Summary
20. (54:25) Homework
Thanks a lot, You are a such generous , unique person for exerted efforts to let others get skilled. please do not stop ,we evaluate and esteem your hard work, If I want to write my real impression about you ,I need to to author a textbook.
+Mohammad Abdallah Hamad Thank you are the VERY kind words! They mean a lot because I do put a LOT of hard work into trying to make the World a Better Place through teaching Excel!
Sorry for posing after this hasn't had feedback for over 4 years, but HW(6) asks " Add a formula to cell F7 that adds the Sales for Sioux. The correct answer is 336!!!", but the answer provided [=SUMIFS(C4:C8,B4:B8,E4)] doesn't work until you remove trailing spaces from the Criteria and Sales Rep data.
However the formula [=SUMIF(B4:B8,TRIM(E4)&"*",C4:C8)] works with the bad data, and the SUMIFS function doesn't work with that Criteria (TRIM function with &"*").
Which leads me to a question. I see you typically use the ???IFS version of a function rather than the ???IF version, even when there's only one criteria needed as in this example. In this case SUMIFS doesn't work, but typically they both give the same answer. Any reason for using the IFS versions other than maybe not having to switch between the two as the IF version's criteria us usually not the same as the IFS version so it's one less thing to remember (which I totally get!).
Thanks - as usual your Videos are the BEST - bar none. No dis to Mr. Excel who is great, but I love that you put in the extra effort to have Start and Finished files, to say nothing of the PDF notes. Really outstanding work!
Honestly, you are unbelievable... Fantastic... wonderful.... I keep watching ur video like a movie for hours and hours....
Awesome job... your series is THE Best one , even comparing with the paid ones....
THANKS A LOTT..
Words cannot express the impact of your contribution to the global community, as well as our gratitude towards such contribution.
Glad to help!!!!
Sir you should really consider running advertisements! We would not mind at all!!!
Thank you : )
Hey Mike at 13:03 You used dollar to put currency formatting but when I am doing the same it doesn't show dollar sign rather it shows Indian currency as I belongs to India.. Btw love to your Advance playlist
Glad you like the playlist : ) Thanks for the note about the Indian currency!
@@excelisfun yup. But I mean it shows Indian currency in my worksheet when I am doing that dollar formula to put number formatting at 13:03 is that normal? Or how I am able to change it?
Amazing video, thank you for your efforts!
You are welcome!
Thank you so much!!! I really learn a lot especially I'm into accounting...
thank u so much for sharing your knowledge :) You're such a great teacher
You are welcome!
For Future Reference
@16:53 VLOOKUP functions and Array Constant
@28:12 Array Calculation / Array Formula
Excel has the golden rule, you have the golden knowledge.
Thanks : )
i wish I had this back in 2000 when I started in Excel !! Amazing!
+Kevin Lehrbass Glad you like it, Kevin!!!
+ExcelIsFun P.S. I wish that I had something like this when I started also...
Hello Mike, Your videos are just awesome.
Just wanted you to check that in your file "Busn218Project01.xlsm" the gross pay comes out to be $9587.38 but you have mentioned it as
$9587.42. Please check it and tell me if I am doing something wrong.
Btw I am liking and commenting on all of your videos Just as you say.
I am 3 years late to the party for this..... tryna be proficient at excel so thanks so much for being a great instructor and mentor. Secondly those fucking spaces piss me off. Made a lot of my answers not make sense when I was 100% sure of the formula. Really tested my illiteracy on this one. Thank YOU!
I am lazy to put my comments on video but your selflessness compelled me to write a comment. You are the true example of the purpose of the internet was created, to share and empower others with knowledge. God Bless You.
Thank you! I am glad that the videos help. But I must tell you after 10 years of giving for free and having a hard time supporting the effort, it is about to end. If only more of us humans could have been NOT lazy and click Thumbs Up to promote this video, I would not have to stop making videos. Notice that for this video, only 1 every 100 viewers clicked Thumbs Up... Maybe, just maybe your turn will inspire others too, and there will be a wave of Thumbs Up on all 3000 videos I have posted, then maybe I won't have to stop. BTW Nayan, I 100% have worked hard in my belief that I could use the internet to reach the world, but the support to help finance it just never came...
I have seen many other sites promoting excel for free internet community service and marketing while they have paid version for live class for those who prefer a personal coach who can clear their doubts. You are a great teacher and I am sure your paid version will be more hit with real professional and novice. why dont you give it a try?.
In example no. 11, if we were to add top 30, 50 or more then we have to put all numbers with commas in array or there's any shortcut for that?
Watched. Refreshment tour. Thank you very much again.
This is the highest quality training content I have ever worked through, and we are so lucky to have access to it for free. Thank you so much!
Free!?!?! I do charge a Thumbs Up lol You are welcome for the fee fun!
@@excelisfun sir gervin if i finish this highline videos can i say that i have advanced skills in excel in interview?
@@xyvz1142 Yes, this Highline class is super advanced!
Mate You have done an excellent job, and with the notes: a very thorough and impressive job. I've used excel for 15 years, and I'm still picking up shortcuts from your teaching. Chris H
Glad the video and notes and Excel workbook help : )
Hey there! Thanks for the excellent new videos.
How do I add a data validation drop-down to cell A4 as you did in HW 8?
Also, I can't figure out HW 3 for the life of me. I wrote the exact same formula you have in your answer sheet, but it still comes up $0.00 for total sales (for Sioux and Sue). I did a little hack, and instead of using a cell reference, I simply inserted the argument "Sioux" --> =SUMIFS(C4:C30,B4:B30,"Sioux"), which does yield the same correct answer. Why won't it work with the cell reference? FYI - I checked formatting and possible formula issues, but even when I copy your answer formula into the cell it still results in $0.00.
UPDATE: Having the exact same issue with HW 10. Writing the same COUNTIFS formulas but getting "O" for Count. =COUNTIFS($B$5:$B$12,D5). Can't figure out why this is happening here, too.
Thanks!
Hi. In 39 min. he explains how to do that. There is a space after text. Sioux and Sue both has space after them. So you need to delet the space.
Hi. select the A5 cell. From the ribbon go to DATA==> DATA VALIDATION ==> DATA VALIDATION, from the window allow: Choose "list" , tab tab and go to source simly drag down B9 to B15. than click ok.
Awesome. Thanks! Who else noticed that the assumptions on the bottom of the sheet of 6.20% and 1.45% are the employee contributions to for social security and medicare respectively?
I did. O, wait, i don't count ; )
Amazingly awesome, thank you very much for all your efforts
+Hicham Hadj You are welcome!
This is fantastic!!! thank you soooooo much for putting in so much effort for our benefit. Wonderful stuff thanks again .... the only problem is I cant seem to download the files/notes. nothing happens when I click on the "open" or " Save" buttons. Pls help !!!
Awesome! Never saw something like this. Keep up your good work. Thanks again!
I am glad that you like the video! Thank you for the support with your comment, Thumbs Up and Sub : )
agree with you.
Mike, I have a XL file with around 20 worksheets. Each WS contains a list (around 100 records) of email connections (incl first and last name and business name of each connection). What I need is a summary of all the data (Email address only) in those worksheets that tells me how many time a unique email address appears within all the individual worksheets. I.E. who connects most down to who connects least. Which of your videos will help me with this. thanks
hey EIF! In Sheet HW(8) the drop-down cell should be created in A5 instead of A4 as the questions states. Keep up the great work. A am learning more and more each video.
So the vlookup constant is not really necessary if we are okay with having the reference table. Right now while helping out my dad I've just created a separate sheet for all the references. That provides the convenience of adding or removing items whenever needed. Am I right?
how would we ask a question for you to try and answer? Because im trying to make this little tool on excel and i just can not figure it out :( .
really loved your video, but you talked too slow, and i have to 1.5 times faster to play it. but thanks a lot, learnt heaps from you.
Thank U
You are welcome, Master Jack!!!! I am so glad you are on to the next class : )
Thought I knew a lot about formulas, but I still learned something new! Thanks!
Really excel is fun ..when we have techers like you ❤️
So talented, so gifted!
You have really the reason to be proud of you!
Your teaching skills and passion to do it are spectacular!
Thank You!
Thank you very much. I completed the "Office 2016 Class: Excel, Word, Power Point, Access, Windows Explorer, The Basics" videos/notes you created. "Highline Complete Excel 2016 Class: Comprehensive Excel: Calculations & Data Analysis" is defiantly more aggressive and challenging. I like the transition. Eye opening examples. Greatly appreciated.
You are welcome! Be sure to say thanks with a Thumbs Up and Comment on each vid that you watch : )
Nicely done again, Mike. At 31:08 I figured out a way to display the values of a resultant array and display them as a total; try this =TEXTJOIN("+",,LARGE(C111:C119,{1;2;3}))&" = "&SUM(LARGE(C111:C119,{1;2;3}))
+DRSteele , Yes, your formula does work! But we already knew that with the amazing new TEXTJOIN!!!! :) But, when teaching people array functions, that is a good way to tell this story so that they "get" what an array is...
Incredible !!! I could not believe this comprehensive Excel teaching. I have been using excel many years, but know very little in fact. Thumbs up ! I like the teaching style and the materials. Thank you so so much Mike. Yan from Vancouver Canada
You are welcome! Thanks for the Thumbs Up and Sub!
This is myy first comment, definitely Subscribed the first time I watching the video 08, what can I say, incredidible!!!!!Thank you!
You are welcome! If you watch the whole series, it will present many of the amazing things that you can do with Excel!
Incredible, you actually make me believe, even just for 55:02 minutes that EXCEL IS FUN. You do such a great job with these videos, the workbooks, and everything.
+Tim Kind I am glad that the video help you have fun with Excel!!! (How to make these? Love what I do and lots of hard work...)
This is an absolutely excellent resource for those of us beyond post secondary who are looking to learn excel taught by someone who knows how to teach, as opposed to reading it out of a "How To" book without any hands on practice or demonstration. Thank you very much for posting this and the exercise files.
On that note, I wanted to ask if there was any way to obtain a solution for the Project files that accompany each of the Weeks? I understand this is/was a university course, so would totally get it if you couldn't, however it would be handy for those of us following the TH-cam playlist and trying to complete the project to have a method to check our work, or to see the formula that is necessary if we are stuck. For instance, in the Week 1 project file - Busn218Project01.xlsm, my gross pay is off by $0.04, and I'm having a heck of a time figuring out the solution to determine hours worked per employee.
Whether or not there's something that can be done for that, I am still very impressed with this set of TH-cam instructional videos, and have already learned plenty enough to keep me engaged. Thank you very much. You're right - Excel IS fun
And, after writing that and revisiting the project file, the =SUMIFS formula came to mind, which I think is the correct formula I need :)
OK Guys my head is hurting with Home Work HW9 i cannot get your answers Sioux Radcoolinator and Battalion Smith i think your wrong in the calculations.
Sioux Radcoolinator should be 7:15 min not 7:25 & Battalion Smith should be 10:15. Even if i count on my fingers i cannot get what Excel shoves out. Tellme what i am doing wrong please.
The calculations are not wrong, +Dale Holden. Hours must be a whole number in order to multiply by an hourly wage. Yes, it is 7 hours 15 minutes, but if you want a whole number, it is =7+15/60 = 7.25. Did you watch the last video #1? This is a series for a class. The #1 video teaches all about Time Math. If you are going to make me work by answering your questions, at least hit the Thumbs Up button. :)
+ExcelIsFun Mike, i watch all your videos and give you big thumbs up:) I even sent you a tip which you didn't know in your Conditional Formatting class you used arrow down 4 times i told you page down :)
Cheers for your reply anyway love your courses
Dale
+Dale Holden You are welcome! Thanks for the Thumbs Up and of course thanks for that GREAT tip about backspace - I teach that one often!!! :)
I am a lab scientist for 10 years and am looking to become an analyst in healthcare industry. Thank you so much for the genius lectures. I've learn a lot about excel from you and it's like watching fun movie ;)
It is like a fun movie because Excel is so much fun!! I am glad that the videos can help you transition to a new job. Thanks for the Thumbs Up and Sub : )
Thank you again Mike! A great summary of Excel formulas and a swift recap of what was covered in the Basics course. I never knew Array Formulas existed until today!
You are welcome, and I am happy that you learned about array formulas : )
I have gone through two of the videos in this series and have instantly fell in love with the teaching style of the instructor. The name of the channel: 'Excel is Fun' , in itself is quite interesting, appealing and attractive and took all my fears away. Icing on the cake are the available downloads, homework sheets and infinite enthusiasm and energy of the motivated instructor!! Truely speaking, will remain indebted for having found this top grade resource available to all, free of cost!!!!
You are welcome for the video. Thank you for your kind words!!! You can relive the indebtedness that you feel with your support by clicking Thumbs Up and commenting on each video you watch and Subscribe : )
Great instructor! Awesome video! Incredibly smooth! Thanks, man!
You are welcome!
Great Video: If I have pressed the F9 key, save and close the document. How can I revert back my cell references to see my actual formula. It seems F9 did the damage.
You can not get back after entering and closing. That is why EVER time i have ever done F9 in a video, I also show Ctrl + Z. F9 is only a temporary thing, you must always undo BEFORE you even enter the formula, let along close.
Amazing - specially the mentioning of varied error messages one gets in excel. Hats off to your voluntary services, Sir!!
You are welcome, silvia!!!!
10 dislikes from the 10 different Excel related TH-cam channels.
Thank you for your support, Aditya : )
At least, we know that people who click thumbs down are confused about what is a right and what is a duty ; )
@@excelisfun So glad that I chose your channel to learn everything about Excel and Data analysis.
@@Aditya_9696 : )
Amazing video! I really appreciate your effort for putting all these valuable knowledges together and share them on TH-cam for free. You have helped and are helping a lot of people including me. I can't say enough thank you for what you have done:)
You are welcome! Thanks for your Sub and Thumbs Up on each video : )
Thank you so much for hard working
You are an awesome teacher. Very articulate in your lectures in making us understand how excel thinks. Keep it up man.
Glad you like the videos! Thanks for the Thumbs Up and Sub!
I don't know how to explain the joy I have watching your videos. God bless you
Glad it is joyful for you, EBUKA!!!!
Home work problem 3. I have checked that my formula is the same is the answer key but it still calculates to 0. Any help please?
It was the "Spaces"!
Thanks so much for this video series. I am in an Excel class now and all your shortcut videos and this series, in particular, is so awesome. Thanks again!
You are welcome!! Thank you for the support with your comment, Thumbs Up and Sub : )
very helpful, to be honest your teaching methods are way better than the other paid resources, thank you very much for your kind efforts.
Glad it was helpful, kamran!!! Thank you for your support : )
Excellent
EXCELlent!!! : )
Thank you again - these classes online are wonderful - deeply appreciated.
You are welcome! I deeply appreciate your support with a Thumbs Up and comment on each video that you watch : )
Cheers Mike. Thank You!
You are welcome, Joshua : ) : )
Awesome work I am learning so many little tricks that will speed up my workflow.
Glad it helps with the workflow!! Thank you for the support with your comment, Thumbs Up and Sub : )
Good exercises! One improvement maybe, on the Formula Examples, Ex 7. you ask us to use the type of formula "lookup formula", and it works well =LOOKUP(C51,$F$51:$G$53,$G$51:$G$53), but in the text next on the right side, you mention vlookup function, and in the example answer, you use the vlookup formula, which seems more complex and you hardcode the range values in the formula itself, which is not recommended as mentioned in your previous course. Anyhow, thank you for all of this!
For example 7, LOOKUP function is not introduced until video #11, and If you listen to the video I set up the situation and say for this example the numbers will not change and it was not possible to put the table in the cells - thus in that case it does not violate Excel's Golden Rule. It is not uncommon where numbers for lookup do not change and the template creator cannot put a lookup table in the cells next to the template and so the Array Constant is the perfect solution. I am not sure what you mean by "more complex" when you refer to the VLOOKUP.
More complex as in more argument to the function { }, in fact, until this very exercise I never knew the basic function lookup existed. I've been using the vlookup for years now. Anyway, I just felt confused a bit, with the title vs the comments, vs the answer. I thought you would show the basic first before getting to the vlookup. Next: Chapter 3!
Thank you very much for the incredible videos! Kudos from Hungary
You are welcome!
The homework for this video was a fun challenge! Thanks!
You are welcome for the fun homework. When you start to have this much fun in this class, Stephanie, it means your are turning into a real Excel Pro : ) : )
Your lessons are very comprehensive and easy to follow. Keep it up!
Glad the videos help! Thanks for your support with Sub and Thumbs Up on each video : )
A lot of good stuff in here, even for an Excel expert. I learned a few new things!
+John Johnson I am glad that there were some Excel Knowledge Nuggets for you!!!
Your voice is soothing to listen to, and these videos are terrific! Thank you
+Alnasrlina I am glad that the videos help!
Simply Amazing! Mike is always the best!
Glad it was amazing for you, Hassan! Thanks for your support with your comment, Thumbs Up and Sub : )
Thank you so much for your hard work and share your knowledge with us :)
You are welcome! Thank you for your support with comments, Sub and Thumbs Up : )
Thank you again for your excellent video!
You are welcome, Carmen!!! Thanks for your support on each video with your comments, thumbs ups and of course your Sub : )
This is amazing, thank you Mike!! I learned something new today. I can't wait to do the homework.
+pokk70 Great! I am glad that the videos are useful for you!
Thank you very much.
This is a good video with tons of great formula tips!!!!
Thank you very much.
You are welcome!!!!
Thanks, really appreciate your content.
You are welcome for the content, Kevin! Thanks for your support with your comment, Thumbs Up and Sub : )
Vey Nice Class. The Video is precise to tackle the Errors.
+D Mishra Glad you like it!
Hello there, This is Anil, i want to learn the excel from beginning to advanced level, could you tell me from which play list of your channel should i start ?
This one:
th-cam.com/play/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw.html
thanx bro....Thanks a Lot for quick reply
INTENSE!
+Oz du Soleil You watched the whole thing that fast!?!?!? Man I need your speed cuz it took me days to make this...
Only one word- Awesome
+Gopal Kumar Glad you like it!
thank you so much sir you are really amazing
I am glad that you like the videos.
I really appreciate every video you upload, you have taught me so much and there is so much to learn! right now I'm applying to jobs and the only skill that has had me hired before is my proficiency in excel thanks to your videos!
You are welcome! I am glad that the videos help : )
it sucks that microsoft has the mac version of office so behind in terms of functionality, I was trying to practice the dashboard of one of your videos and it doesn't have the power query installed by default (nor free versions of it).
would you in a future consider building a dashboard from a mac computer for users like me that own a mac and get lost ?
Because Mac has none of the BI tools, it is really not an potion any more for business users. I have no idea why they made the decision to NOT include all the cool new tools. My first Excel back in the early 1990s was a Mac. I loved it. But now, it is just not an option.
thank you so much
You are welcome so much!!!
I am practicing on the project and I can't seem to figure out where I am missing .04 My total is 9,587.38 Any help would be greatly appreciated.
Did you use the ROUND function? Did you check the solution that I provided - it will have an example of a correct solution : )
O, wait a second, the project is for students in my class and so there is no solution... But anyway, try using teh ROUND Function - that will fix the 0.04 off issue : )
Thank you so much, I am trying to do as much as I can to learn and become very proficient. I love learning excel...
Please help spread the word about the education that I provide here at TH-cam at no cost!!! : ) It often times surprises me that this free education (with notes and practice problems) is not watched more. So each comment and Thumbs Up helps, and be sure to Bell-Icon Subscribe and tell all your friends : ) AND, have fun learning, C1 Chef!!!!
P.S. is your name C1 Chef, like the cell in Excel C1? Which makes you an Excel Chef ? ; )
super Awesome....
You are welcome!
Thanks mike!
You are welcome, Nikoloz!!!
Thanks Mike.
+krn14242 You are welcome, WRH!!!
Parabéns Mike.Obrigado.
Glad you like it!
Epic video!!
Glad you like it! Thanks for the support with your comment, Thumbs Up and Sub : )
thanx sir..
+Manas Behera You are welcome!
Nice one
Glad you like it! Thanks for the support with your comment, Thumbs Up and Sub : )
Thanks
You are welcome, clifford : )
Perfect
You are welcome!
Cool!!!
Glad you like it! Thanks for the Thumbs Up and Sub : )
Thank uuuuu awsome great
+Mohamed Chakroun You are welcome!
you sir , you are the best
I am glad that you like the videos! Thank you for the support with your comment, Thumbs Up and Sub : )
❤
Glad you love it, Lamees!!!!!
What does
@@excelisfun From Urban Dictionary: "When viewed sideways, appears to be the outline of a heart. Used in an expression of love." Because I'm loving watching your videos. (
@@PieterSteens Thanks for the love Pieter!!!