We've been using dynamic arrays in Excel for a while and there are some things that would make them a bit more useful! Today, a look at a new add-in from Excel MVP Charles Williams that allows you to add totals to dynamic arrays. To learn more about the add-in, visit FastExcel V4 SpeedTools
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2310.
Charles Williams improves dynamic arrays.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, here, early in February, there's a great new product coming out and I love this quote from Excel MVP Charles Williams.
"Once you've had a chance to use dynamic arrays for awhile, you start to realize what new features would make dynamic arrays a bit more useful".
Charles released an add-in and after I had used it for about an hour, I wrote back to Charles I said.
It took a team of developers in Redmond over a year to develop dynamic arrays.
You made them an order of magnitude better?
How many developers do you have working on this?
Charles says "Yeah, it was me working for about 3 weeks". In case you don't know Charles. Here's a photo Charles.
He's a legend. I mean he he worked on the original IBM PC.
You know like he says the bits of his code are still in the operating system that any of us are using today.
Just amazingly smart guy and his new speed tools introduce over 100 powerful fast-calculating functions for Excel and the ones that I am going to show you today.
These floating and static tootalling for dynamic arrays are just a piece of this.
Charles would love to make millions of dollars in this, but that's not why he wrote it.
I mean, he just says this needs to be in Excel and he's offered it to the Excel team.
But even if the Excel team would accept, I mean with the red tape and bureaucracy it'll take.. years.
So in the meantime, yeah, you can buy this add-in from Charles.
The link is down there in the YouTube description.
Alright, let's just talk about how cool dynamic arrays are, but you know the inherent problem with them.
So right here I have a formula that is going to change its size based on some input cells.
There's a RANDARRAY function in there, it's returning everything and you know this is great.
But let's say we needed totals at the bottom edge of this, so I select all my cells plus one extra row plus one extra column Press Alt+Equals to put those AutoSum functions in.
Well that's fine, as long as that array stays the current size.
This little nomenclature here, SUM of D6 hashtag, well, that automatically grows, but that's just a single cell, right?
So as soon as I change this to 4 rows, five columns, what's going to happen?
I get a SPILL error because the formula here says "Hey look I need to write values and you have totals there so you have to go get rid of your totals in order to make this work and re-enter the totals".
That's going to be miserable. So, here's what Charles gave us.
He said why hey, I'll give you a function called TOTALS( like this, and what it does is it takes your array an it puts... watch row 10 ...
watch right here.
When I press enter and it puts the totals down there below the data, right?
So there's my RANDARRAY and as the array grows, so we go from 4 rows to 6 rows, the totals automatically move with it. Isn't that cool right?
So it's always floating down there and Charles figures.
I guess this total row is most popular. But what if you want a total column?
Well, change TOTALS to HTOTALS.
And we get a column at the end.
What if you want to column total column at the right hand side and a total row at the bottom?
And we just wrap TOTALS.
Around HTOTALS.
And we now have the ability to put a total row and a total column below a dynamic array, even if that dynamic array changes size.
So let's make this 8 rows by 13 columns and the totals are sitting out there working right. So there's 745. 745.
Choose these 402? 402. Choose the whole thing.
It works on the fly 5659? 5659.
This is just one of the functions.
It is funny when Charles showed this to the MVP community.
One of my favorite Excel MVP's Roger Govier in Wales, says Charles, that's fine but I don't put my totals at the bottom.
I always put my totals at the top and to the left so Charles it goes back into the development studio.
Comes out a few hours later with SUMCOLS of an array and that'll allow Roger or anyone who likes the totals at the top to have a floating rowd here.
And SUMROWS for the floating total at the left. Again, this will change.
So if we go to 11 rows And nine columns.
Get rid of my little note there.
And then it will automatically extend with the range.
Beautiful. Here's another one.
So here's a FILTER function were filtering to either Team=red or Team=blue?
So I choose either red or blue and I get a different number of results returned.
I want to total but I only want totals on column 4 and column 6, so this formula = the TOTALS formula is very cool. It has the ability to exclude columns.
For exclude_cols I say hey, don't give me totals on column 1, 2, 3, or 5, and now I get totals just on 4 and 6. And, check this out.
Haven't even tried this yet. Right now it's defaulting to 9 which equals SUM.
You'll recognize that's the arguments that are in Subtotals, and a lot of other things, so 1 for average.
2 for count, 3 for Count Numbers. All 11 functions are available there.
If you would need an average at the bottom or something like that.
Hey, this is the point where normally I ask you to buy my book and I do have a chapter in this book about Charles' Add-In. I think it's that important.
But today I think you should go out and support Charles Williams.
Now the other thing we're going to do is we're going to try and get an Excel.UserVoice going here to try and encourage Microsoft to include these, and sooner rather than later, that would be a really good thing.
So go ahead, click that "i" in the top right hand corner and you'll get to a page where you can link out the Charles site, check out the new add-in. They just released this week.
If you like what you see here, please subscribe and ring that bell.
Feel free to post any questions or comments down in the YouTube comments below. Well, thanks for stopping by.
We'll see you next time for another. netcast from MrExcel.
Charles Williams improves dynamic arrays.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, here, early in February, there's a great new product coming out and I love this quote from Excel MVP Charles Williams.
"Once you've had a chance to use dynamic arrays for awhile, you start to realize what new features would make dynamic arrays a bit more useful".
Charles released an add-in and after I had used it for about an hour, I wrote back to Charles I said.
It took a team of developers in Redmond over a year to develop dynamic arrays.
You made them an order of magnitude better?
How many developers do you have working on this?
Charles says "Yeah, it was me working for about 3 weeks". In case you don't know Charles. Here's a photo Charles.
He's a legend. I mean he he worked on the original IBM PC.
You know like he says the bits of his code are still in the operating system that any of us are using today.
Just amazingly smart guy and his new speed tools introduce over 100 powerful fast-calculating functions for Excel and the ones that I am going to show you today.
These floating and static tootalling for dynamic arrays are just a piece of this.
Charles would love to make millions of dollars in this, but that's not why he wrote it.
I mean, he just says this needs to be in Excel and he's offered it to the Excel team.
But even if the Excel team would accept, I mean with the red tape and bureaucracy it'll take.. years.
So in the meantime, yeah, you can buy this add-in from Charles.
The link is down there in the YouTube description.
Alright, let's just talk about how cool dynamic arrays are, but you know the inherent problem with them.
So right here I have a formula that is going to change its size based on some input cells.
There's a RANDARRAY function in there, it's returning everything and you know this is great.
But let's say we needed totals at the bottom edge of this, so I select all my cells plus one extra row plus one extra column Press Alt+Equals to put those AutoSum functions in.
Well that's fine, as long as that array stays the current size.
This little nomenclature here, SUM of D6 hashtag, well, that automatically grows, but that's just a single cell, right?
So as soon as I change this to 4 rows, five columns, what's going to happen?
I get a SPILL error because the formula here says "Hey look I need to write values and you have totals there so you have to go get rid of your totals in order to make this work and re-enter the totals".
That's going to be miserable. So, here's what Charles gave us.
He said why hey, I'll give you a function called TOTALS( like this, and what it does is it takes your array an it puts... watch row 10 ...
watch right here.
When I press enter and it puts the totals down there below the data, right?
So there's my RANDARRAY and as the array grows, so we go from 4 rows to 6 rows, the totals automatically move with it. Isn't that cool right?
So it's always floating down there and Charles figures.
I guess this total row is most popular. But what if you want a total column?
Well, change TOTALS to HTOTALS.
And we get a column at the end.
What if you want to column total column at the right hand side and a total row at the bottom?
And we just wrap TOTALS.
Around HTOTALS.
And we now have the ability to put a total row and a total column below a dynamic array, even if that dynamic array changes size.
So let's make this 8 rows by 13 columns and the totals are sitting out there working right. So there's 745. 745.
Choose these 402? 402. Choose the whole thing.
It works on the fly 5659? 5659.
This is just one of the functions.
It is funny when Charles showed this to the MVP community.
One of my favorite Excel MVP's Roger Govier in Wales, says Charles, that's fine but I don't put my totals at the bottom.
I always put my totals at the top and to the left so Charles it goes back into the development studio.
Comes out a few hours later with SUMCOLS of an array and that'll allow Roger or anyone who likes the totals at the top to have a floating rowd here.
And SUMROWS for the floating total at the left. Again, this will change.
So if we go to 11 rows And nine columns.
Get rid of my little note there.
And then it will automatically extend with the range.
Beautiful. Here's another one.
So here's a FILTER function were filtering to either Team=red or Team=blue?
So I choose either red or blue and I get a different number of results returned.
I want to total but I only want totals on column 4 and column 6, so this formula = the TOTALS formula is very cool. It has the ability to exclude columns.
For exclude_cols I say hey, don't give me totals on column 1, 2, 3, or 5, and now I get totals just on 4 and 6. And, check this out.
Haven't even tried this yet. Right now it's defaulting to 9 which equals SUM.
You'll recognize that's the arguments that are in Subtotals, and a lot of other things, so 1 for average.
2 for count, 3 for Count Numbers. All 11 functions are available there.
If you would need an average at the bottom or something like that.
Hey, this is the point where normally I ask you to buy my book and I do have a chapter in this book about Charles' Add-In. I think it's that important.
But today I think you should go out and support Charles Williams.
Now the other thing we're going to do is we're going to try and get an Excel.UserVoice going here to try and encourage Microsoft to include these, and sooner rather than later, that would be a really good thing.
So go ahead, click that "i" in the top right hand corner and you'll get to a page where you can link out the Charles site, check out the new add-in. They just released this week.
If you like what you see here, please subscribe and ring that bell.
Feel free to post any questions or comments down in the YouTube comments below. Well, thanks for stopping by.
We'll see you next time for another. netcast from MrExcel.
