Rob from Cambridge needs to divide nine cells by nine other cells and sum them up. In Episode 1141, we look at an array formula to solve this problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey welcome back, it's another MrExcel netcast, I'm Bill Jelen.
And today's questions sent in by Rob from England, Rob is trying to do and array formula, and these are tricky when you're new to array formulas.
Rob has a series of values here, and for each value he wants to divide it by the exchange rate.
Now he has a formula here, that's not an array formula, it is kind of ugly, dividing each one B5/B1+C5/C1, and on and on.
And here was Rob's attempt at doing an array formula, and I press Ctrl+Shift+Enter here, which is correct.
He said “Hey, let's take all of these values and divide by all of these values.” And well, you know, that's kind of on the right path.
What you're going to get from an array formula if you build it correctly, is you're going to get a series of values, and so you ALWAYS have to put that in some sort of a wrapper function.
In this case, a great wrapper function would be SUM, so I'm going to come here and say =SUM, and we'll get rid of that parentheses.
So B5:J5 divided by, and here, because I'm assuming we're going to want to copy this down, I'm going to put some dollar signs in.
So we'll get rid of that opening parenthesis, B$1:J$1, and that closing parenthesis closes out the SUM.
Now if you just press Enter here, you're not going to get the right result, you're going to get a #VALUE! error, but the trick is to hold down Ctrl+Shift and press Enter, and what Excel is going to do?
it's going to do those nine divisions, and then SUM the whole thing up.
Now once we have it, grab the fill handle and copy down, and we have a very nice simple formula that does all the divisions and sums them up, certainly faster than this.
So, Rob, hey, great idea using an array formula, just a little bit weird how we had to do it there, make sure to use the SUM function as a wrapper.
Alright hey, want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey welcome back, it's another MrExcel netcast, I'm Bill Jelen.
And today's questions sent in by Rob from England, Rob is trying to do and array formula, and these are tricky when you're new to array formulas.
Rob has a series of values here, and for each value he wants to divide it by the exchange rate.
Now he has a formula here, that's not an array formula, it is kind of ugly, dividing each one B5/B1+C5/C1, and on and on.
And here was Rob's attempt at doing an array formula, and I press Ctrl+Shift+Enter here, which is correct.
He said “Hey, let's take all of these values and divide by all of these values.” And well, you know, that's kind of on the right path.
What you're going to get from an array formula if you build it correctly, is you're going to get a series of values, and so you ALWAYS have to put that in some sort of a wrapper function.
In this case, a great wrapper function would be SUM, so I'm going to come here and say =SUM, and we'll get rid of that parentheses.
So B5:J5 divided by, and here, because I'm assuming we're going to want to copy this down, I'm going to put some dollar signs in.
So we'll get rid of that opening parenthesis, B$1:J$1, and that closing parenthesis closes out the SUM.
Now if you just press Enter here, you're not going to get the right result, you're going to get a #VALUE! error, but the trick is to hold down Ctrl+Shift and press Enter, and what Excel is going to do?
it's going to do those nine divisions, and then SUM the whole thing up.
Now once we have it, grab the fill handle and copy down, and we have a very nice simple formula that does all the divisions and sums them up, certainly faster than this.
So, Rob, hey, great idea using an array formula, just a little bit weird how we had to do it there, make sure to use the SUM function as a wrapper.
Alright hey, want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
