i am so glad that you came back to watch the videos in this class - because so much is packed into this class, and there is no way to get it all the first time through if you are learning for the first time. That is why videos at RUclips are so good= you can come back and watch them any time : )
Yes, this is one COMPREHENSIVE video. Not for the faint of heart - just for Excelers like you who want to know all the pieces and how they fit together : )
Just can't figure out how I landed on this channel. The most excellent tutorial ever in Excel Am looking forward to a promo after applying this concepts. A big thanks to Girvin for the excellent works. May the almighty bless your good works abundantly.
D Functions are a hidden treasure from the past. Not many people know about them. But if you do not need to copy the formula and can afford the worksheet rel-estate for a criteria area, they are THE best : )
You are welcome! Yes, you can thank me in a simple way, which most people do not bother to help me, just click that thumbs up and leave a small comment on each video that you learn from : ) Thanks, houssam!!!
Love your videos :) Like many people watching it again after 2 years and so much i had forgotten or didn't get the first time. Thank you. one comment though the table on the "and sheet" is buggy on office 2021 for exemple d functions don't work and yet work perfectly on office 2019. Another thing on array formula ctrl enter is not needed anymore with sum it works with no error :) Anyway big fan thanks !
Hello sir mike everything is going well.. I really like this video but at 29:16 the D function having problem when every I complete my formula and hit enter it show's zero and in D sum and D average it show value divided by zero???
Hello Michael, I have a question. At 48:26, you have said as it is an array formula, we either need to put =Sumproduct(sumifs...) or we can hit Ctrl+Shift+Enter to get the accurate answer. I chose Ctrl+Shift+enter as I did not want to input =sumproduct in front of sumifs, but I got a different result (714,637) instead of 3,515,181. Obviously, when I put =sumproduct as you did I got the same result. If you do not mind, could you please explain why that happened? Thank you.
Oh I get it, we need to add up the array results of the sumifs function at the end as well! The last video on array formulas really confused me and had to watch it 3 times so still trying to understand it. Why Excel does not show the result in three numbers, but it only shows one whole number at the end of sumifs?. What I mean is, why does it not display the answers like it does when we hit F9 so it is clear that we received an array result?
SUMIFS delivers an array of answers. It is an array formula because we put multiple items into criteria argument to force SUMIFS to deliver an array of answers. If your goal is to add, then put it in SUM and use Ctrl + Shift + Enter. You must use Ctrl + Shift + Enter because SUM function is not programmed to understand arrays unless you use that keyboard. On the other hand, SUMPRODUCT is programmed to automatically understand arrays without a special key stroke. If you are in Miscroosft 365 Excel, then just put it in SUM and press Enter because the new Calculation engine understands everything as an array formula.
So thorough...what a good teacher you are! Mike, I get so confused when doing nested IF statements. At 36:00 or so, could you just multiply two arrays (or many) in the one IF statement? Like for E7, CtrlShiftEnter: =STDEV.S(IF((A4:A10=E4)*(B4:B10=F4),C4:C10))
+DRSteele You multiply when zeros will not interfere with calculation. You use Nested IF when you need the false to filter out values and not have zeros in the calculations. Zeros mess calculations like Standard Deviation up. It is easy to remember, DRSteele, when you nest IF functions: when you get to vale_if_false and there are still more than one things left, you know that you need more IF functions.
+DRSteele Your account does not let me send you e-mails... I don't see that you clicked the Thumbs Up button!?!? Did you? For free videos and knowledge you always have to click the Thumbs Up! :)
Thank you so much Mike for another awesome video!!! I just have a quick question regarding at 25:15, actually the function for the last row does not include the upper limit $10,000, and if there is exact $10,000 sales in the data set, it wont b included in the calculation, what do you say? :)
This video is helping me out a lot, but I have a question. When it comes to create names from selections will the names auto update? Right now I have a lot of dummy text filling in spots.
Hi Mike, a quick question on D functions - i tried to key in the exact same formula as you, but got 0 for both Dcount, Dsum and #DIV/0 error for both DAvg and Dstd. Any idea what went wrong? =DCOUNT(A1:G2000,K75,J72:N73) =DSUM(A1:G2000,K75,J72:N73) =DAVERAGE(A1:G2000,K75,J72:N73) =DSTDEVP(A1:G2000,K75,J72:N73)
I think I found a solution assuming you're in part of the world that uses d/m/y, although I'm sure there is a more efficient fix. I believe it has something to do with the cell formatting on the dates in the criteria field (i.e >=10/1/2013 and
Thanks Mike for the good example how to Count cells between two numbers with COUNTIFS. I was always wondering (same with IF function) why Excel doesn't have BETWEEN function like SQL has.
Hi Mike I am able to remove the startup macro thru the following steps: after starting excel 2016 go to file tab then new then select blank workbook then check to make sure that no macros has been inserted in all the sheets then save the new workbook as template name it as "book" then copy the template to the following loaction C:\Users\Username\AppData\Roaming\Microsoft\Excel\XLSTART next time when i open a new workbook the macro was not there save a sheet as template then name it as "sheet" copy the sheet template to the same location as mention above next time when i inserted a sheet in a workbook no macro was there either please try if it works thank you
+Alex Go in my copy of excel 2016 everytime i start a new workbook, or insert a new sheet in a workbook, excel automatic insert a macro in each sheets with this macro Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False ActiveCell.EntireColumn.AutoFit ActiveCell.ColumnWidth = ActiveCell.ColumnWidth + 2 Application.ScreenUpdating = True End Sub this enable autofit of the column width every time i type something in a cell, but this action effectively clear the undo stack after every entry i.e. no more(unable to do) undo after every entry how to turn off this new feature in excel 2016? thanks
+Alex Go Wow!!! I did not know that this was occurring. I just had no idea that in Excel 2016 this was happening. But this explains why every time I try to use Power Query, it says "can't put output on a Macro Sheet". I will have to post a question to mrexcel.com/forum or send an e-mail to Microsoft to ask why. Thanks, Alex Go, it is good that you posted because i was not aware that this was going on even though i was having trouble with it. Hopefully we can figure this out. If you figure something out, please post back and I will do the same to you.
You brought up HW4 from the previous videos workbook. I added the following formula the right of "Region". *I added a column to the right of "Region" labeled "Sales Team". =CHOOSE(IFNA(MATCH(A4,$G$4:$J$4,0),IFNA(MATCH(A4,$G$5:$J$5,0),IFNA(MATCH(A4,$G$6:$J$6,0),IFNA(MATCH(A4,$G$7:$J$7,0),"Error")))),$G$3,$H$3,$I$3,$J$3) The intent is to only have to create one pivot table. Is there an easier way than this formula to identify what team a Sales Rep is on?
Is there anyone who understands exactly what means about the following narrative around 1:04:30? It is said, "we have the same database comma, it doesn’t matter which field because they both have text..."
Great video, I appreciate all of them. One issue I've run into with using stdev.s with an if function is that blank cells are treated as a numerical zero in the calculation whereas the stdev.s function will just ignore a blank cell thus resulting in two different answers. Probably not an issue that comes up often but it has gotten me when I'm analyzing incomplete data tables.
+sjn 72 , Well, if you use IF without last argument, then any records that do not match criteria will receive a FALSE value and STDEV.S is programmed to ignore the FALSE values -- so it should be working... Maybe you need to add an extra condition to filter out empty cells... like NOT(ISBLANK(range) or range0, or something like that.
+ExcelIsFun Thanks, that worked. I added a range"" condition and that took care of it. I just checked it using F9 on the formula and a blank record gets a value of 0 and not FALSE, hence why the calculation is off. Using the range"" condition gives it a value of FALSE and it is ignored as you mentioned. Thanks again.
+Laza Lazarevic That is not something that I have done. There is a way (but it is complicated), so you will have to Googke it or post to the best Excel question site: mrexcel.com/forum
I have a question from Example 11 & 12: If array table shows identical records from the same record(s) throughout, is there a way to not show it in the Drop down List? Thanks for the great vid, as always!
Hi :) Your videos are great, thanks very much. I really need to get my self-acquainted with these topics, but I can't find the excel workbook on your website. Can you help me?
Sorry being a pain xD But I can't find it. When I click on the link it goes to your web page, where I can find everything except this one. There is a gap between video05 and video07. Maybe I'm missing something. I'll Thumbs Up every single one because I'm truly glued on your videos. Thanks for sharing you know how with all of us. You're a star xD :)
Cant fint the Busn218-Video06.xlsm file on your page. Tere is only: Busn218-Video05.xlsm Highline Excel 2016 Class 06 Conditional Calculations with Excel Formulas AND & OR Criteria.pdf Busn218-Video07.xlsx Highline Excel 2016 Class 07 Excel 2016 MAXIFS, MINIFS & IFS Functions for Conditional Calculations.pdf
trying to understand what row or column to lock or lock both the column and the row in a formula has to probably be the most confusing thing in excel for me
There is a reason why it's called Highline Excel. This is indeed an advanced level. Thanks for all you do always, Mr Mike.
You are welcome, Abdulsamad!!!
The Average(ifs(or formula makes so much since three years after I originally watched this one. Thanks Mike.
i am so glad that you came back to watch the videos in this class - because so much is packed into this class, and there is no way to get it all the first time through if you are learning for the first time. That is why videos at RUclips are so good= you can come back and watch them any time : )
The most helpful excel tutorials on the Internet!!! Thank you for all your hard work.
You are welcome! Thanks for the Thumbs Up!!!!
Thank you Mike for all the hard work in putting together all this information! You are a very good trainer! Super-like!
It's of pro pro level ❤️ . .I need go through it again. Thanks a lot🙏
Videos like this are meant to be here at RUclips forever, so each time you need the knowledge, you can just boomerang back : )
@@excelisfun indeed 💯
Stumbled across this video Mike, I must have missed this one. Epic!
Yes, this is one COMPREHENSIVE video. Not for the faint of heart - just for Excelers like you who want to know all the pieces and how they fit together : )
Just can't figure out how I landed on this channel. The most excellent tutorial ever in Excel
Am looking forward to a promo after applying this concepts.
A big thanks to Girvin for the excellent works. May the almighty bless your good works abundantly.
Glad the videos helps, Charles!!! Thanks for your support : )
D function..that's so cool! It is going to be one of my favorite function going forwards:)
D Functions are a hidden treasure from the past. Not many people know about them. But if you do not need to copy the formula and can afford the worksheet rel-estate for a criteria area, they are THE best : )
Another great video.... by whom? by Mr Mike..the father of excel for me you do something incredible......
+Mohamed Chakroun Thank you for the kind words! I am glad that you like the videos!
I can not Thank you enough for your amazing work, God bless you
You are welcome! Yes, you can thank me in a simple way, which most people do not bother to help me, just click that thumbs up and leave a small comment on each video that you learn from : ) Thanks, houssam!!!
@@excelisfun I always do
@@houssamayoubi8715 Thanks, houssam : ) : ) : )
Watched. Refreshment tour. Thank you very much again.
May God bless you my excel father
Love your videos :) Like many people watching it again after 2 years and so much i had forgotten or didn't get the first time. Thank you. one comment though the table on the "and sheet" is buggy on office 2021 for exemple d functions don't work and yet work perfectly on office 2019. Another thing on array formula ctrl enter is not needed anymore with sum it works with no error :)
Anyway big fan thanks !
That is too bad Microsoft disallowed D functions in Excel 2021. Glad you like all the content, david!!!
You are just simply amazing really really amazing I have learned so much from you
Glad you like the videos!
THAT'S SWEEEEET!! incredible page excellsfun.. just you make it fun dude!! thank you again
YES!!! Glad it was fun for you! It is fun for me making the videos! Thanks for the Thumbs Up and Sub!!! : )
Thank you very much every day!
You are welcome every day : )
thank you so much
You are welcome!!!
Thank you Teacher. God bless you.
I have used Excel for about 20 years and know a lot about many of these topics, but there is always something in here that I didn't know.
Glad the video helps! Thanks for the support with your comment, Thumbs Up and Sub : )
Hi Mike, at 51:42, why not use AVERAGEIFS() straight away instead of nested AVERAGE(IF()) ? is there any reason?
Hello sir mike everything is going well.. I really like this video but at 29:16 the D function having problem when every I complete my formula and hit enter it show's zero and in D sum and D average it show value divided by zero???
What is your formula?
Wonderful work !
Totally agree!
Hello Michael, I have a question. At 48:26, you have said as it is an array formula, we either need to put =Sumproduct(sumifs...) or we can hit Ctrl+Shift+Enter to get the accurate answer. I chose Ctrl+Shift+enter as I did not want to input =sumproduct in front of sumifs, but I got a different result (714,637) instead of 3,515,181. Obviously, when I put =sumproduct as you did I got the same result. If you do not mind, could you please explain why that happened?
Thank you.
Oh I get it, we need to add up the array results of the sumifs function at the end as well! The last video on array formulas really confused me and had to watch it 3 times so still trying to understand it. Why Excel does not show the result in three numbers, but it only shows one whole number at the end of sumifs?. What I mean is, why does it not display the answers like it does when we hit F9 so it is clear that we received an array result?
SUMIFS delivers an array of answers. It is an array formula because we put multiple items into criteria argument to force SUMIFS to deliver an array of answers. If your goal is to add, then put it in SUM and use Ctrl + Shift + Enter. You must use Ctrl + Shift + Enter because SUM function is not programmed to understand arrays unless you use that keyboard. On the other hand, SUMPRODUCT is programmed to automatically understand arrays without a special key stroke. If you are in Miscroosft 365 Excel, then just put it in SUM and press Enter because the new Calculation engine understands everything as an array formula.
@@excelisfun Thanks!
Great video and great teaching (watch out for desert vs. dessert)
Thanks again, Mike!
You are welcome : )
Another excellent video!
Glad it was EXCELlent for your, Doris!!!!
So thorough...what a good teacher you are! Mike, I get so confused when doing nested IF statements. At 36:00 or so, could you just multiply two arrays (or many) in the one IF statement? Like for E7, CtrlShiftEnter: =STDEV.S(IF((A4:A10=E4)*(B4:B10=F4),C4:C10))
+DRSteele You multiply when zeros will not interfere with calculation. You use Nested IF when you need the false to filter out values and not have zeros in the calculations. Zeros mess calculations like Standard Deviation up. It is easy to remember, DRSteele, when you nest IF functions: when you get to vale_if_false and there are still more than one things left, you know that you need more IF functions.
+DRSteele Your account does not let me send you e-mails... I don't see that you clicked the Thumbs Up button!?!? Did you? For free videos and knowledge you always have to click the Thumbs Up! :)
+ExcelIsFun I'd like to receive an email. So I clicked thumbsup. Is that all there is to it?
it's kind of sophisticated, well done
+jimmy jo Glad you like it!
Thank you so much Mike for another awesome video!!!
I just have a quick question regarding at 25:15, actually the function for the last row does not include the upper limit $10,000, and if there is exact $10,000 sales in the data set, it wont b included in the calculation, what do you say? :)
Thank you for the great tutorial
You are welcome!
Is there a possibility to use SUMIF with two different criteria using OR logic i.e. sum up sales if region = south or salesrep = Gigi?
I really like these videos.
Glad you like them! Be sure to support the channel with Thumbs Up on each video that you watch! and thanks for the comment : )
Thank you so much for sharing your knowledge !!!!!!
You are welcome!
This video is helping me out a lot, but I have a question. When it comes to create names from selections will the names auto update? Right now I have a lot of dummy text filling in spots.
Hi Mike, a quick question on D functions - i tried to key in the exact same formula as you, but got 0 for both Dcount, Dsum and #DIV/0 error for both DAvg and Dstd. Any idea what went wrong?
=DCOUNT(A1:G2000,K75,J72:N73)
=DSUM(A1:G2000,K75,J72:N73)
=DAVERAGE(A1:G2000,K75,J72:N73)
=DSTDEVP(A1:G2000,K75,J72:N73)
I think I found a solution assuming you're in part of the world that uses d/m/y, although I'm sure there is a more efficient fix.
I believe it has something to do with the cell formatting on the dates in the criteria field (i.e >=10/1/2013 and
Thanks Mike
+krn14242 You are welcome, Most Awesome WRH!!!
Thanks Mike for the good example how to Count cells between two numbers with COUNTIFS. I was always wondering (same with IF function) why Excel doesn't have BETWEEN function like SQL has.
+GodIsAlive! You are welcome! Thanks for clicking Thumbs Up!
how we can highlight alphanumeric no. by using conditional formatting
The shortcut timers on 61:10 and up don't work, I guess due to missing the hourly component?
1:01:10
1:05:00
1:12:42
1:20:17
1:22:18
+Aab Baab Thanks for the editing tip!! I posted this late at night with bluury eyes, so I need the help :). I will fix it!
Hi Mike
I am able to remove the startup macro thru the following steps:
after starting excel 2016
go to file tab then new then select blank workbook
then check to make sure that no macros has been inserted in all the sheets
then save the new workbook as template name it as "book"
then copy the template to the following loaction
C:\Users\Username\AppData\Roaming\Microsoft\Excel\XLSTART
next time when i open a new workbook the macro was not there
save a sheet as template then name it as "sheet"
copy the sheet template to the same location as mention above
next time when i inserted a sheet in a workbook no macro was there either
please try if it works
thank you
+Alex Go , I don't have that particular problem, so I did not need to do it. I am glad that you found a solution...
Hi Mike
how to turn off the autofit macro that excel 2016 inserted during start up?
regards
+Alex Go I have not noticed an "autofit macro that excel 2016 inserted during start up", what does it do?, what does that mean?
+Alex Go
in my copy of excel 2016
everytime i start a new workbook, or insert a new sheet in a workbook, excel automatic insert a macro in each sheets with this macro
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
ActiveCell.EntireColumn.AutoFit
ActiveCell.ColumnWidth = ActiveCell.ColumnWidth + 2
Application.ScreenUpdating = True
End Sub
this enable autofit of the column width every time i type something in a cell, but this action effectively clear the undo stack after every entry i.e. no more(unable to do) undo after every entry
how to turn off this new feature in excel 2016?
thanks
+Alex Go Wow!!! I did not know that this was occurring. I just had no idea that in Excel 2016 this was happening. But this explains why every time I try to use Power Query, it says "can't put output on a Macro Sheet". I will have to post a question to mrexcel.com/forum or send an e-mail to Microsoft to ask why. Thanks, Alex Go, it is good that you posted because i was not aware that this was going on even though i was having trouble with it. Hopefully we can figure this out. If you figure something out, please post back and I will do the same to you.
+ExcelIsFun I sent an e-mail to Microsoft so hopefully they will have some insight into what is going on...
You brought up HW4 from the previous videos workbook. I added the following formula the right of "Region".
*I added a column to the right of "Region" labeled "Sales Team".
=CHOOSE(IFNA(MATCH(A4,$G$4:$J$4,0),IFNA(MATCH(A4,$G$5:$J$5,0),IFNA(MATCH(A4,$G$6:$J$6,0),IFNA(MATCH(A4,$G$7:$J$7,0),"Error")))),$G$3,$H$3,$I$3,$J$3)
The intent is to only have to create one pivot table. Is there an easier way than this formula to identify what team a Sales Rep is on?
Is there anyone who understands exactly what means about the following narrative around 1:04:30? It is said, "we have the same database comma, it doesn’t matter which field because they both have text..."
It means that you can count either column because they both contain text.
Great video, I appreciate all of them.
One issue I've run into with using stdev.s with an if function is that blank cells are treated as a numerical zero in the calculation whereas the stdev.s function will just ignore a blank cell thus resulting in two different answers. Probably not an issue that comes up often but it has gotten me when I'm analyzing incomplete data tables.
+sjn 72 , Well, if you use IF without last argument, then any records that do not match criteria will receive a FALSE value and STDEV.S is programmed to ignore the FALSE values -- so it should be working... Maybe you need to add an extra condition to filter out empty cells... like NOT(ISBLANK(range) or range0, or something like that.
+sjn 72 Thanks for the Thumbs Up when you appreciate all of them!
+ExcelIsFun Thanks, that worked. I added a range"" condition and that took care of it. I just checked it using F9 on the formula and a blank record gets a value of 0 and not FALSE, hence why the calculation is off. Using the range"" condition gives it a value of FALSE and it is ignored as you mentioned. Thanks again.
+sjn 72 You are welcome!
Another great video :)
Thanx
Do you have any idea how to do date calculations for dates before 1/1/1900?
+Laza Lazarevic That is not something that I have done. There is a way (but it is complicated), so you will have to Googke it or post to the best Excel question site: mrexcel.com/forum
ExcelIsFun
Ok thanx
I have a question from Example 11 & 12: If array table shows identical records from the same record(s) throughout, is there a way to not show it in the Drop down List? Thanks for the great vid, as always!
how do I download your spreadsheets?
Nice video, thanks a lot :)
Thanks, this is awesome
You are welcome! Thanks for your Sub and Thumbs Up!
hi, it's mind blowing.
can you help me out to find the formula's wordings for below:
If N1 is between D2 and E2 to insert number 1
Epic video.
Hi :)
Your videos are great, thanks very much.
I really need to get my self-acquainted with these topics, but I can't find the excel workbook on your website.
Can you help me?
Watch the first few seconds =of the video and then click on link below video. And be sure to click that Thumbs Up and Sub : )
Sorry being a pain xD But I can't find it. When I click on the link it goes to your web page, where I can find everything except this one. There is a gap between video05 and video07. Maybe I'm missing something.
I'll Thumbs Up every single one because I'm truly glued on your videos. Thanks for sharing you know how with all of us. You're a star xD
:)
Click link below video and below the Video #6, the file is: Busn218-Video06Start.xlsx
Done
you are awesome.... ;-)
+Mammen Mathews Glad you like the videos!
EXCELLENT
Glad it is EXCELlent for you, MAHENDRAN!!!!
Cant fint the Busn218-Video06.xlsm file on your page. Tere is only:
Busn218-Video05.xlsm
Highline Excel 2016 Class 06 Conditional Calculations with Excel Formulas AND & OR Criteria.pdf
Busn218-Video07.xlsx
Highline Excel 2016 Class 07 Excel 2016 MAXIFS, MINIFS & IFS Functions for Conditional Calculations.pdf
Great
+Vivek Dubey Glad you like it!
Thanks
You are welcome, clifford!!!! : )
trying to understand what row or column to lock or lock both the column and the row in a formula has to probably be the most confusing thing in excel for me
Thanks for ur videos,pls let me know how to display formula written in some other cell
nice one
+Manas Behera Glad you like it!
ExcelIsFun "Awesome"
Glad you like it! Thanks for the Thumbs Up!
Godlike