Don’t Use PivotTables. Use COUNTIFS or FREQUNCY Functions Instead. Excel Magic Trick 1849
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1847-...
Learn about how PivotTables cannot include upper limit when grouping number data with decimals. COUNTIFS and FREQUNCY Functions do include upper limits when grouping numbers into upper and lower limit categories.
Topics:
1. (00:00) Introduction
2. (00:06) PivotTable
3. (01:45) COUNTIFS
4. (02:49) Text Labels for Formula Report
5. (03:25) Find Feature to create labels for PivotTable
6. (03:56) FREQUENCY Array Function
7. (05:15) Summary
8. (05:30) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup
Love you Mike! Can’t believe the enthusiasm you have for Excel and teaching, it’s something very special ❤
I am happy to make special fun Excel videos for you and the rest of the Team!!!!
it may not gave been the main purpose of the video but I'd never thought of using find and replace on a pivot, what joy 😊😊
Yes!!! Ctrl + H Rules in Excel and Word : )
Thanks Mike, another great video!!!!! I didn't know about the upper limit issue in pivot tables.
The Microsoft programmers had to pick one side to include, and they chose the lower limit. For most projects it doesn't matter because the patter will still be revealed, but if you need to include upper limit... : )
Fantastic! I learn something new EVERY time!!!
Awesome!!! You and I both: I learn something new each time too : )
Great video!! ✌
If I will find time this week I will publish a study about my FRQ function, concept plus a tone of examples.
- can deal with lower or upper limits (pivot or countifs functionality + frequency functionality in one)
- can calculate frequencies of non numeric values (ignored by FREQUENCY)
- results can have same framework of bins array (if bins array is 2D, FREQUENCY results are always a clm vector)
- calculates for any bins array and/or sorted unique bins intervals that can be listed next to the frequencies.....and more ✌😊
You are working for Microsoft now, right lol
You, da man!!!!!!
Excel is Lambda Fun!!!!
@@excelisfun Not yet ! 😂
Mr ExcelisMagic Sir ,
You are the greatest excel lover ever known to me.
Though the problem has been solved earlier , you always brings up alternative with definite explanation as to what, why and how
Please never stop posting as it brings in me passion after always watching your videos
I am very happy that you enjoy what I post. It has been 15 years 362 days so far that I have posted, and I plan to keep doing it forever, or as long as I can (which ever comes first) : ) : )
Informative and new way for me
Glad this helps : )
THANKS. EXCELLENT VIDEO & EXPLANATION:)
Wow ... i spent alot of time to use grouping in pivot table reports ... and my numbers included decimals ... and of course it didn't work correctly ...now i konw ... thanks Mike
Yes, it is important to know this fact about Pivots : ) : )
Thanks Mike, another great video. I have noticed that Excel can do some very odd things to the unwarey so it is always a pretty good idea to double check upper bounds, lower bounds and any calculation that includes conditionals or many nested functions.
I 100% agree. Very smart, Roy! I always do projects at least two ways to try assure that things are correct.
@@excelisfun I do the same and have even been known to resort to pen, paper and brain-power when calcution results look off but is probably just me showing my age :D
@@roywilson9580I am that way too: sometimes paper and pencil to check : )
Thank you so much!
You are welcome so much!!!
Super helpful. Thank you!
You are welcome!!!
Thanks amazing Mike for this EXCELlent video.
You are most welcome, Fellow EXCELlent Teacher : ) : ) : )
Thank you Mike. Can you also create similar dynamic pivot tables by using GROUPBY and PIVOTBY?
There is not an automatic groupby feature in those functions, but it would be cool if Microsoft added one : )
@@excelisfun
a raw and not very tested version for thought and improvement could be
=GROUPBY(CEILING.MATH(C6:C25 , 500) , C6:C25 , COUNT)
and "opposite" option for include/exclude
=GROUPBY(FLOOR.MATH(C6:C25 , 500) , C6:C25 , COUNT)
Thanks Mike.
You are welcome, Formula Guy John!!!
First😊
First Place Trophy for you : ) : )
wow great :D
Glad it is great for you !!!!
I think using =Counta(filter()) would also do the trick in every situation, but it is bit slow in performance
You are right. COUNTIFS, SUMIFS and the like will almost always calculate faster than direct array operations.
My takeaway is that I didn't know one could group categories in pivot table the way you did.
And I didn't know the frequency function. I'm more likely to use it than countifs in similar cases.
You inclination is good because COUNTIFS always gives the most flexibility with creating just the categories that you want.
Great video Mike, We can explore the same using sumproduct as well..
You can use SUMPRODUCT, but as I researched and wrote about in my 2012 book, Ctrl + Shift + Enter: Mastering Excel Array Formulas, COUNTIFS will almost always calculate more quickly than SUMPRODUCT because COUNTIFS use underlying code whereas SUMPRODUCT has to make direct array calculations, row-by-row.
Here is how you would do it with SUMPRODUCT:
=SUMPRODUCT(--($C$6:$C$25>=E25),--($C$6:$C$25
It took me about 20 minutes to get the new file uploaded, but it is there now.
@@excelisfun thank you Mike..... I've read your cntr+ shift+enter book back in 2015 and have been following you since 2014....you have been my greatest inspiration and have learnt so much following you.... Thank you for everything... 😊
@@prabhusundar3006 You are welcome for everything : )
Wao! that trick over the pivot table doing the labels thing, I didn't know it. Mike., kudos men!
Ctrl + H is fun : )
Hey, Mike..... Maybe something like this to enhance the FREQUENCY approach to skip the extra category?: =CHOOSEROWS(FREQUENCY(C6:C25,F15:F18),SEQUENCE(1,4))
you can use =drop(FREQUENCY(C6:C25,F15:F18), -1) to remove extra category,
Thanks, Ron!! Thank extra category always gives people trouble.
@@nguyentuan826 Thanks Tuan Nguyen!! Microsoft is so good to us with DROD and TAKE : )
Great video Mike 📹 👍. One question : i didnt quite get what u mean that the decimal numbers in pivot tables are not included ?
Decimals numbers induce the Pivot to group this way. If you have integers, the categories are different: there is no duplicate numbers in the upper limit of one category and the lower limit of the next category. Categories go like this: 0-499, 500-999 and so on, rather than 0-500, 500-1000 and so on.
I added an integer example to the download workbook file : )
It took me about 20 minutes to get the new file uploaded, but it is there now.
@@excelisfun ah I see now that is very interesting I didn't know that before.
@@excelisfun thank u so much Mike for adding the interger example. This makes everything very clear 👌
Sir what is the work in company for Excel. What work does company give for Excel jobs
Hello Sir Pls help me
I'm facing the one condition use Google sheet make pivot table need click pivot table showing data as same sheet not new sheet data visible same sheet pls❤help
I am sorry, I do not know how to use Google Sheets : (
Need some corrections in countif formula because pivot table has different values
That does happen often. The specific details always lead to a solution ; )
Changed 500.00 to 500, PivotTable still not working.
So it's not just the decimal format or something else? Thanks.
I do not know what you mean. What is not working?
I just added an example of integer and decimal numbers to the download file on the 1849 (an) worksheet tab. You can check that out.
If you tried Number Formatting, you might be missing a crucial fundamental concept in Excel: Number Formatting is a façade - Number Formatting never changes the actual number, it just displays it on the surface in a different way. Number Formatting is not seen by formulas and PivotTables - Pivots and Formulas always see and act on the underlying number.
How to download free Excel laptop plz share me link