I watched this video many, many times. Love how you get the total in the array. But on your webpage about this topic, you talk about using that logic for subtotals also. For almost a year I watched this video trying to visualize including subtotals. Yesterday, it clicked. Referring back to the end of the video, you talked about 3 total rows. And boom, it clicked. I can set up subtotals going up above the staged array, along with using the MAXIFS() function and +.5 in the index/row column. The key was that your video always showed the efforts in the top rows of the sheet. But if you move the staging array down to row 20, you have almost 15 rows above to do the subtotal lines and calculations. I love it when a plan comes together.
Hi Jon - I just wanted to say how ingenious this solution is. We're leveraging dynamic arrays extensively in our product and I was about to implement an entire set of custom functions in C# to handle subtotalling. You just eliminated the need for it. Thanks!
I tried so many times to get a workable solution for this with dynamic arrays. But failed time after time. You’ve only gone and done it!!! Great work, amazing!🥇
Thanks Mark! It was a simple typo that lead me down the rabbit hole to this solution. That's the fun part about Excel. You never know what you'll stumble upon and the possibilities are endless!
I'm a huge fan of totals on top in pivots and tables but I really like how you calculated columns in a spilled array so that was a great approach compared to the way I was doing it. Thanks.
Ingenious, Jon! I “solve” this by writing aggregate formulas above column headers in spilled arrays. I’ve come to prefer that display method (v. at bottom). But, I’m sure that I’ll need to display at the bottom &/or provide in-spilled-array subtotals. Your technique here will enable that. Thanks!
Learned some great "by the way" tips as well. Thanks. This is some really amazing extrapolation of how to apply these formula's. Simple once I see it but would not have considered it on my own.
Great trick. I have been putting the totals at the top any time I want to add totals to a spilled array. This is sooo much better. I love the trick of #spilledArray:anotherCell = new expanded array. I am sure this will help me simplify some of the formulas I have been writing. Thanks for sharing
Hi Jon.. another great video and very clever use of helper columns and re-sorting from top to bottom to position the totals dynamically at the bottom of the spilled range. Thanks for sharing it.. will come in very handy! Thumbs up!!
Another great video - I do love how you break things down and explain them. I'm always looking at better ways to dynamically summarise data and this is a really clever solution. I always am the go-to person in work for excel queries and your videos inspired me to start my own beginners excel channel. Keep making great content the way you do! :)
Hi again Jon.. thought I would share these.. 3 formulas that will produce your first table (Dynamic Array Total Row) without the need of a helper table: Cell L6: =IF(SEQUENCE(COUNTA(FILTER(tblSales5[Order Date],tblSales5[Customer Name]=M3))+1)>COUNTA(FILTER(tblSales5[Order Date],tblSales5[Customer Name]=M3)),"TOTAL",FILTER(tblSales5[Order Date],tblSales5[Customer Name]=M3)) Cell M6: =FILTER(tblSales5[[Customer Name]:[Product Name]],tblSales5[Customer Name]=M3) Cell O6: =IF(SEQUENCE(COUNTA(FILTER(tblSales5[Amount],tblSales5[Customer Name]=M3))+1)>COUNTA(FILTER(tblSales5[Amount],tblSales5[Customer Name]=M3)),SUMIFS(tblSales5[Amount],tblSales5[Customer Name],M3),FILTER(tblSales5[Amount],tblSales5[Customer Name]=M3)) The formulas rely on a technique I learned from Mike Girvin. They dynamically put the "TOTAL" label at the bottom of the spilled range in column L and the SUM of the filtered range at the bottom of the spilled range in column O. It's fun to get it all from a dynamic formula, but actually I think that your solution is more practical and versatile, as you demonstrated in the Multiple Total Rows example. I tried to work that out with the method above, but get stuck on the total, average and count of the filtered rows above. I can get the labels stacked into the bottom of the spilled range in column Q, but I have no solution for how to sum the filtered range of each column individually and spilled horizontally. I'll keep playing with it. Anyway.. fun to share some progress and innovation with these new functions. Thanks for all of your insights and the great learning opportunities at your RUclips channel, web site and in your courses. Thumbs up!!
Hi Wayne, Thanks for sharing your solutions. Awesome! 🙌 I agree that it's fun to try different techniques to solve this relatively complex problem. Hopefully Microsoft will make it a bit easier in the future. Thanks again! 🙂
Thank you Jon so much for another excellent video, and for sharing the workbook as it is extremely helpful to go through it bit by bit (an old brain like mine needs to review it multiple times for it to sink in!) Cheers!
Hi Jon.. and since I figured it out, here are the formulas to dynamically spill the results for your third example.. the cross-tab with row, column and grand totals for the selected rep.. no helper table needed: Cell AH6: =IF(SEQUENCE(COUNTA(UNIQUE(tblData[Customer Name]))+1)>COUNTA(UNIQUE(tblData[Customer Name])),"TOTAL",SORT(UNIQUE(tblData[Customer Name]))) Cell AI5: =IF(SEQUENCE(,COUNTA(UNIQUE(tblData[Year-Month]))+1)>COUNTA(UNIQUE(tblData[Year-Month])),"TOTAL",TRANSPOSE(SORT(UNIQUE(tblData[Year-Month])))) Cell AI6: =IF(AH6#&AI5#="TOTALTOTAL",SUMIFS(tblData[Revenue],tblData[Rep Name],'Total Row and Column'!AI3),IF(AI5#="TOTAL",SUMIFS(tblData[Revenue],tblData[Customer Name],'Total Row and Column'!AH6#,tblData[Rep Name],'Total Row and Column'!AI3),IF(AH6#="TOTAL",SUMIFS(tblData[Revenue],tblData[Year-Month],'Total Row and Column'!AI5#,tblData[Rep Name],'Total Row and Column'!AI3),SUMIFS(tblData[Revenue],tblData[Customer Name],'Total Row and Column'!AH6#,tblData[Year-Month],'Total Row and Column'!AI5#,tblData[Rep Name],'Total Row and Column'!AI3)))) These are definitely gnarly formulas.. but.. they get surprisingly easy to construct once you break them down and build them over and over again. I hope some find them helpful.. I've learned a lot tinkering with them and making them work on your examples. Thanks again for all the great videos, web site, blogs and classes. I always learn something new at Excel Campus. Thumbs up!!
Awesome! I love that you took on the challenge to simplify this, and more importantly, learn in the process. I believe that it's during these "tinkering" times that we get to be creative, and find additional techniques that we can use in other scenarios. And thank you for your contributions to the channel and Excel Campus as well. We are all learning from each other and I'm grateful for your support and participation. 🙌
@@ExcelCampus Thanks Jon.. was definitely a fun one and your assessment is absolutely correct. The majority of the learning and creativity comes in the tinkering, hitting dead ends, starting over, trying something new, etc. It can be frustrating, but like so many things in life.. worth it for the satisfaction of eventually succeeding. You channel, web site, blog posts, classes, etc. are all invaluable resources to me for guidance, information, inspiration, etc. So.. as always.. thanks for all that you do to help me improve myself and my skills. We all win when we share :)) Thumbs up!!
Great video. Could you run through the technique that you used to create the greyed-out dropdown arrow that references back to the cell with the list validation? It looks like it's a picture, but I'm not sure. Thanks.
Very cool technique, although I go out of my way to have totals at the top of all my pivots and tables anyway. I was thinking I could use this and start the sequence at 2 and have the total row an index of 1 to accomplish my particular view preference. Often times I have a lot of data that requires scrolling down to see total. I even do this with pivots where I create a header column with the name Grand Total and then in my pivot make it the top row and add subtotals. Works like a charm.
I appreciate your efforts in getting even a 71-year-old newbie getting interested in Excel.You really have a flair for teaching . Thank you.I am a retired journalist and want to analyse wage factory data. I will be thankful If you can show me by way of video (your own links ) or text (your own blog links) ib lhow to go about doing it, that would be great. It will be a great lesson for me to analyse reports . Example: Wage raw data in cvc format How to display in pivot tables and graphs? I have database with the following 4 columnsStart: (general ) 2020.08.04 19:20:00 End: (general ) 2020.08.04 19:20:00 Day of week (general) : 1 Wages (general): amount in 3 digit and 1 digit decimal. How do I format to use it for filtering and counting besides displaying in report and a quick comparision table format? Total count needed for: Every Quarter all compared with same Quarter based on 7 groups of hours clubbed together Week, all compared with same week based on 7 groups of hours clubbed together Day,all compared with same day based on 7 groups of hours (filtered by wage amount) clubbed together 7 groups of hours clubbed together 6 hours (from to) , 1 hour, 2 hour, 5 hours, 1 hour, 2 hours, 3 hours 7 groups of hours filtered by Maximum. Maximum is defined as all wages less than a defined amount. Minimum is defined as all wages more than a defined amount. Intention is to find and display by way of illustrations which hours are attractive to workers and which hours are not liked by workers. basically, a seasonal pattern .
This is a super video. I really find extremely useful and used it at work. The only thing is when creating a chart it worked perfectly with the offset function. However, the data table in the chart was displaying different data label signs only currency instead of currency and percentage. It seems that the data labels of the charts do not follow the data labels set by the condonation formatting but rather the original formatting of the cells is what is being used. I am not sure if there is way to make the data labels to use the conditional formatting data labels instead of the original cells ? Any ideas?
Great video!!Simple and straight forward!!. What if the real estate of the spreadsheet does not allow you for helper tables? Came out with a Single cell formula for everything. Looks complicated but it isn't. =LET(x,M3,tbl,tblSales,hdr,tblSales[#Headers],cstm,tblSales[Customer Name], flt,FILTER(tbl,cstm=x), tot,SUM(INDEX(flt,,4)), lr,{"Total","",""}, nr,ROWS(flt)+2,sqr,SEQUENCE(nr), arr,INDEX(flt,SEQUENCE(nr-1,,0),SEQUENCE(,4)), rslt,IFS(SEQUENCE(nr,4)=nr*4,tot,sqr=1,hdr,sqr=nr,lr,TRUE,arr), rslt) This is only for the single Total row, can be done for multiple total rows, practically for any result array configuration .
WOW! That's a very cool use for the new LET function. Thanks for sharing. As I mention at the end of the video it's probably best to put the helper cells (staging area) on a separate sheet, but it's great to see other ways to go about it. Thanks again! 👍
@@ExcelCampus You're welcome! Always helper stuff is faster and more efficient. The only thing that triggered me in writing single cell formulas is its kind of portability rudimentary similar to VBA. We elaborate a complex formula in a single cell doing something, copying it as a string in other workbooks , changing only the input variable references and good to go. Even created a defined name with a formula that creates the formula text, that can be copied and activated as formula. LET is amazing ,fast , easy to share and understand.
Hi Jon, great videos. I'm not sure where I can ask a general question so I'm going to ask here. I need to create a spreadsheet I am using as an audit form. I have to check whether something has been don or not. If it has not I have assigned points to it. I then need to total those points. Is there a way I can do all of that in Excel?
Hi Corey, Great question! Yes, this can absolutely be done in Excel and there are multiple ways to go about it. For the logic to assign points you can use the IF function or VLOOKUP. Here is a video that explains how to calculate commission rates based on a rate table. This technique can also work for grades or points that have specific tiers. ruclips.net/video/iK_0ctO2PqI/видео.html We also have a Community Forum in our Elevate Excel Training Program where members can post specific questions and get help. excelcampus.com/elevate I hope that helps. Thanks again and have a nice day! 🙂
Yes, I'd recommend using pivot tables. Dynamic Arrays are not available on older versions of Excel, but pivot tables can produce a lot of the same results. I do have a popular video series on pivot tables and dashboards if you are not familiar with them yet. Here is the link. ruclips.net/video/9NUjHBNWe9M/видео.html I hope that helps.
Wouldn't a pivot table be so much easier? I haven't used spill ranges yet (the company where I worked until recently was still clinging to Office 2010 if you can believe that), but I didn't detect anything in the video that couldn't be implemented in a pivot table. Or am I missing something?
I watched this video many, many times. Love how you get the total in the array. But on your webpage about this topic, you talk about using that logic for subtotals also. For almost a year I watched this video trying to visualize including subtotals. Yesterday, it clicked.
Referring back to the end of the video, you talked about 3 total rows. And boom, it clicked. I can set up subtotals going up above the staged array, along with using the MAXIFS() function and +.5 in the index/row column. The key was that your video always showed the efforts in the top rows of the sheet. But if you move the staging array down to row 20, you have almost 15 rows above to do the subtotal lines and calculations. I love it when a plan comes together.
Thank you, after two days of fighting and losing with this, thank you.
Thank you Jon. Always good to have totals at the bottom of dynamic arrays
Thanks Mark! 🙂
Thanks for the video
Great technique..Thanks
Hi Jon - I just wanted to say how ingenious this solution is. We're leveraging dynamic arrays extensively in our product and I was about to implement an entire set of custom functions in C# to handle subtotalling. You just eliminated the need for it. Thanks!
I tried so many times to get a workable solution for this with dynamic arrays. But failed time after time.
You’ve only gone and done it!!! Great work, amazing!🥇
Thanks Mark! It was a simple typo that lead me down the rabbit hole to this solution. That's the fun part about Excel. You never know what you'll stumble upon and the possibilities are endless!
I'm a huge fan of totals on top in pivots and tables but I really like how you calculated columns in a spilled array so that was a great approach compared to the way I was doing it. Thanks.
Ingenious, Jon! I “solve” this by writing aggregate formulas above column headers in spilled arrays. I’ve come to prefer that display method (v. at bottom). But, I’m sure that I’ll need to display at the bottom &/or provide in-spilled-array subtotals. Your technique here will enable that. Thanks!
Thanks Jim! A total row at the top is definitely A LOT easier right now, but hopefully that will change in the future.
Learned some great "by the way" tips as well. Thanks. This is some really amazing extrapolation of how to apply these formula's. Simple once I see it but would not have considered it on my own.
Thanks Susanne! Happy to hear you picked up some additional tips too. 🙌
Great trick. I have been putting the totals at the top any time I want to add totals to a spilled array. This is sooo much better. I love the trick of #spilledArray:anotherCell = new expanded array. I am sure this will help me simplify some of the formulas I have been writing. Thanks for sharing
Hi Jon.. another great video and very clever use of helper columns and re-sorting from top to bottom to position the totals dynamically at the bottom of the spilled range. Thanks for sharing it.. will come in very handy! Thumbs up!!
Glad it was helpful! 😊
Another great video - I do love how you break things down and explain them. I'm always looking at better ways to dynamically summarise data and this is a really clever solution. I always am the go-to person in work for excel queries and your videos inspired me to start my own beginners excel channel. Keep making great content the way you do! :)
Thanks Erika! I appreciate the nice feedback and happy to hear you are inspired by the videos. That's how I started my online journey as well. 😊
Very clever ... thanks alot
Hi again Jon.. thought I would share these.. 3 formulas that will produce your first table (Dynamic Array Total Row) without the need of a helper table:
Cell L6: =IF(SEQUENCE(COUNTA(FILTER(tblSales5[Order Date],tblSales5[Customer Name]=M3))+1)>COUNTA(FILTER(tblSales5[Order Date],tblSales5[Customer Name]=M3)),"TOTAL",FILTER(tblSales5[Order Date],tblSales5[Customer Name]=M3))
Cell M6: =FILTER(tblSales5[[Customer Name]:[Product Name]],tblSales5[Customer Name]=M3)
Cell O6: =IF(SEQUENCE(COUNTA(FILTER(tblSales5[Amount],tblSales5[Customer Name]=M3))+1)>COUNTA(FILTER(tblSales5[Amount],tblSales5[Customer Name]=M3)),SUMIFS(tblSales5[Amount],tblSales5[Customer Name],M3),FILTER(tblSales5[Amount],tblSales5[Customer Name]=M3))
The formulas rely on a technique I learned from Mike Girvin. They dynamically put the "TOTAL" label at the bottom of the spilled range in column L and the SUM of the filtered range at the bottom of the spilled range in column O. It's fun to get it all from a dynamic formula, but actually I think that your solution is more practical and versatile, as you demonstrated in the Multiple Total Rows example. I tried to work that out with the method above, but get stuck on the total, average and count of the filtered rows above. I can get the labels stacked into the bottom of the spilled range in column Q, but I have no solution for how to sum the filtered range of each column individually and spilled horizontally. I'll keep playing with it. Anyway.. fun to share some progress and innovation with these new functions. Thanks for all of your insights and the great learning opportunities at your RUclips channel, web site and in your courses. Thumbs up!!
Hi Wayne,
Thanks for sharing your solutions. Awesome! 🙌
I agree that it's fun to try different techniques to solve this relatively complex problem. Hopefully Microsoft will make it a bit easier in the future.
Thanks again! 🙂
That was a great video! Only one like it I’ve seen so far, keep up the great work.
Awesome, this is exactly what I was looking for, thanks a lot.
Great video! I am learning a lot from you. Thanks for that. The new dynamic arrays are amazing.
Thank you Jon so much for another excellent video, and for sharing the workbook as it is extremely helpful to go through it bit by bit (an old brain like mine needs to review it multiple times for it to sink in!) Cheers!
That was a neat trick Jon! Thanks for the tutorial.
Brilliant Jon, another inspirational video. Many Thanks.
Thanks Kevin! 🙂
Great Tutorial,Really Love The Impressive Dynamic Functions...Thank You Jon :)
Thanks Darryl! Glad you liked it! 🙂
You have great videos. Thanks so much.
It was really creative and clever way.
Thanks.
Thanks! 🙂
Hi Jon.. and since I figured it out, here are the formulas to dynamically spill the results for your third example.. the cross-tab with row, column and grand totals for the selected rep.. no helper table needed:
Cell AH6: =IF(SEQUENCE(COUNTA(UNIQUE(tblData[Customer Name]))+1)>COUNTA(UNIQUE(tblData[Customer Name])),"TOTAL",SORT(UNIQUE(tblData[Customer Name])))
Cell AI5: =IF(SEQUENCE(,COUNTA(UNIQUE(tblData[Year-Month]))+1)>COUNTA(UNIQUE(tblData[Year-Month])),"TOTAL",TRANSPOSE(SORT(UNIQUE(tblData[Year-Month]))))
Cell AI6: =IF(AH6#&AI5#="TOTALTOTAL",SUMIFS(tblData[Revenue],tblData[Rep Name],'Total Row and Column'!AI3),IF(AI5#="TOTAL",SUMIFS(tblData[Revenue],tblData[Customer Name],'Total Row and Column'!AH6#,tblData[Rep Name],'Total Row and Column'!AI3),IF(AH6#="TOTAL",SUMIFS(tblData[Revenue],tblData[Year-Month],'Total Row and Column'!AI5#,tblData[Rep Name],'Total Row and Column'!AI3),SUMIFS(tblData[Revenue],tblData[Customer Name],'Total Row and Column'!AH6#,tblData[Year-Month],'Total Row and Column'!AI5#,tblData[Rep Name],'Total Row and Column'!AI3))))
These are definitely gnarly formulas.. but.. they get surprisingly easy to construct once you break them down and build them over and over again. I hope some find them helpful.. I've learned a lot tinkering with them and making them work on your examples. Thanks again for all the great videos, web site, blogs and classes. I always learn something new at Excel Campus. Thumbs up!!
Awesome! I love that you took on the challenge to simplify this, and more importantly, learn in the process. I believe that it's during these "tinkering" times that we get to be creative, and find additional techniques that we can use in other scenarios.
And thank you for your contributions to the channel and Excel Campus as well. We are all learning from each other and I'm grateful for your support and participation. 🙌
@@ExcelCampus Thanks Jon.. was definitely a fun one and your assessment is absolutely correct. The majority of the learning and creativity comes in the tinkering, hitting dead ends, starting over, trying something new, etc. It can be frustrating, but like so many things in life.. worth it for the satisfaction of eventually succeeding. You channel, web site, blog posts, classes, etc. are all invaluable resources to me for guidance, information, inspiration, etc. So.. as always.. thanks for all that you do to help me improve myself and my skills. We all win when we share :)) Thumbs up!!
Very nice :-) very usefulll
Great video. Very helpful. thx. 😀
Great video. Could you run through the technique that you used to create the greyed-out dropdown arrow that references back to the cell with the list validation? It looks like it's a picture, but I'm not sure. Thanks.
This is really helpful. Thanks a lot
Awesome! Thanks Nader! 🙌
Bravo 👏🏼👏🏼👏🏼
Thanks Wyn! 🙂
Thank you for sharing.
Thanks Miriam! 🙂
Thank you Jon.
Thanks Rodgers! 🙂
Great thought process.
Thanks Gopi! 🙂
Great tip, thanks. Is there any way to limit the number of spilled rows after using a FILTER function?
Great! Thanks a lot
Very cool technique, although I go out of my way to have totals at the top of all my pivots and tables anyway. I was thinking I could use this and start the sequence at 2 and have the total row an index of 1 to accomplish my particular view preference. Often times I have a lot of data that requires scrolling down to see total. I even do this with pivots where I create a header column with the name Grand Total and then in my pivot make it the top row and add subtotals. Works like a charm.
I appreciate your efforts in getting even a 71-year-old newbie getting interested in Excel.You really have a flair for teaching . Thank you.I am a retired journalist and want to analyse wage factory data. I will be thankful If you can show me by way of video (your own links ) or text (your own blog links) ib lhow to go about doing it, that would be great. It will be a great lesson for me to analyse reports .
Example: Wage raw data in cvc format
How to display in pivot tables and graphs?
I have database with the following 4 columnsStart: (general ) 2020.08.04 19:20:00
End: (general ) 2020.08.04 19:20:00
Day of week (general) : 1
Wages (general): amount in 3 digit and 1 digit decimal.
How do I format to use it for filtering and counting besides displaying in report and a quick comparision table format?
Total count needed for:
Every Quarter all compared with same Quarter based on 7 groups of hours clubbed together
Week, all compared with same week based on 7 groups of hours clubbed together
Day,all compared with same day based on 7 groups of hours (filtered by wage amount) clubbed together
7 groups of hours clubbed together
6 hours (from to) , 1 hour, 2 hour, 5 hours, 1 hour, 2 hours, 3 hours
7 groups of hours filtered by Maximum.
Maximum is defined as all wages less than a defined amount. Minimum is defined as all wages more than a defined amount.
Intention is to find and display by way of illustrations which hours are attractive to workers and which hours are not liked by workers.
basically, a seasonal pattern .
Thank you so much. I have a question why not use Pivot Table. Thank again
Very cool!
It worked, but how do we interpret all these symbols under custom accounting formatting: _-R$ * #.##0_-;-R$ * #.##0_-;"";_(@_) ???
This is a super video. I really find extremely useful and used it at work. The only thing is when creating a chart it worked perfectly with the offset function. However, the data table in the chart was displaying different data label signs only currency instead of currency and percentage. It seems that the data labels of the charts do not follow the data labels set by the condonation formatting but rather the original formatting of the cells is what is being used. I am not sure if there is way to make the data labels to use the conditional formatting data labels instead of the original cells ? Any ideas?
Fantastic
Thank you! Cheers! 🍻
Thanks for this, Jon. How do you format visible spilled range?
Great video!!Simple and straight forward!!. What if the real estate of the spreadsheet does not allow you for helper tables? Came out with a Single cell formula for everything. Looks complicated but it isn't.
=LET(x,M3,tbl,tblSales,hdr,tblSales[#Headers],cstm,tblSales[Customer Name],
flt,FILTER(tbl,cstm=x),
tot,SUM(INDEX(flt,,4)),
lr,{"Total","",""},
nr,ROWS(flt)+2,sqr,SEQUENCE(nr),
arr,INDEX(flt,SEQUENCE(nr-1,,0),SEQUENCE(,4)),
rslt,IFS(SEQUENCE(nr,4)=nr*4,tot,sqr=1,hdr,sqr=nr,lr,TRUE,arr),
rslt)
This is only for the single Total row, can be done for multiple total rows, practically for any result array configuration .
WOW! That's a very cool use for the new LET function. Thanks for sharing.
As I mention at the end of the video it's probably best to put the helper cells (staging area) on a separate sheet, but it's great to see other ways to go about it.
Thanks again! 👍
@@ExcelCampus You're welcome! Always helper stuff is faster and more efficient. The only thing that triggered me in writing single cell formulas is its kind of portability rudimentary similar to VBA. We elaborate a complex formula in a single cell doing something, copying it as a string in other workbooks , changing only the input variable references and good to go. Even created a defined name with a formula that creates the formula text, that can be copied and activated as formula. LET is amazing ,fast , easy to share and understand.
Hi Jon, great videos. I'm not sure where I can ask a general question so I'm going to ask here. I need to create a spreadsheet I am using as an audit form. I have to check whether something has been don or not. If it has not I have assigned points to it. I then need to total those points. Is there a way I can do all of that in Excel?
Hi Corey,
Great question! Yes, this can absolutely be done in Excel and there are multiple ways to go about it. For the logic to assign points you can use the IF function or VLOOKUP. Here is a video that explains how to calculate commission rates based on a rate table.
This technique can also work for grades or points that have specific tiers. ruclips.net/video/iK_0ctO2PqI/видео.html
We also have a Community Forum in our Elevate Excel Training Program where members can post specific questions and get help. excelcampus.com/elevate
I hope that helps. Thanks again and have a nice day! 🙂
How can we assign shortcut, ctrl + enter, to command button in userform in excel vba
By the way, can anyone tell me how to create the hidden group EFGHIJK with +/- Unhide/hide as shown at 1:02
any work around for users of Excel 2010 to achieve the same result.
Yes, I'd recommend using pivot tables. Dynamic Arrays are not available on older versions of Excel, but pivot tables can produce a lot of the same results. I do have a popular video series on pivot tables and dashboards if you are not familiar with them yet. Here is the link. ruclips.net/video/9NUjHBNWe9M/видео.html
I hope that helps.
Excel tables also work for this. They are available in 2010. To get the totals, to go the Table Design menu and check the 'Total Row' box.
Wouldn't a pivot table be so much easier? I haven't used spill ranges yet (the company where I worked until recently was still clinging to Office 2010 if you can believe that), but I didn't detect anything in the video that couldn't be implemented in a pivot table. Or am I missing something?
ı need it for old version of excel. formulas doesnt work in my excel.