The classic formula for a multiplication table is =$A2*B$1 which is a great way to teach people about mixed references. However, while reading Mike Girvin's new Ctrl + Shift + Enter book, I ran across this awesome one-formula trick using Matrix Multiplication and the MMULT function.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1732 - Multiplication Table!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well you know, I've shown this before, the multiplication table of equal B$1*$A2, press Ctrl+Enter, and it creates the multiplication table, BUT!
I was working on Mike's book, I was going through doing some indexing, and he has both a book "Ctrl+Shift+Enter", and a DVD.
The DVD is a bunch of videos, and really goes through and answers a lot about array formulas.
And I was reading through, and reading through, and then I saw this great little example of how to create a multiplication table, using an array formula that uses matrix multiplication.
And so that is an function called MMULT.
Now what you have to do, is you have to select a square area, that the multiplication table is going to be in, =MMULT, and the first array will be this column here, comma, and the second array is this column here.
Now this is doing matrix multiplication, which normally is much more complicated than this, but it turns out, press Ctrl+Shift+Enter, and it creates the perfect multiplication table.
Now it has to be square, you have to have 12 numbers down the side, 12 numbers across the top, or 10x10, or 5x5, or 50x50.
It won't work for 10 going across the top and 12 going down the side.
But a cool, cool trick.
So if you're interested in array formulas, check out Mike's new DVD, it's for sale right now at mrexcel.com . If you just go to the MrExcel store, it's there on the first page.
The book is in production right now, will probably be out in about 3 or 4 weeks, but the DVD is good to go and in stock.
Well hey, I want to thank you for stopping by, creating a multiplication table with MMULT, thanks to Mike Girvin for that hot trick from his new book, see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1732 - Multiplication Table!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well you know, I've shown this before, the multiplication table of equal B$1*$A2, press Ctrl+Enter, and it creates the multiplication table, BUT!
I was working on Mike's book, I was going through doing some indexing, and he has both a book "Ctrl+Shift+Enter", and a DVD.
The DVD is a bunch of videos, and really goes through and answers a lot about array formulas.
And I was reading through, and reading through, and then I saw this great little example of how to create a multiplication table, using an array formula that uses matrix multiplication.
And so that is an function called MMULT.
Now what you have to do, is you have to select a square area, that the multiplication table is going to be in, =MMULT, and the first array will be this column here, comma, and the second array is this column here.
Now this is doing matrix multiplication, which normally is much more complicated than this, but it turns out, press Ctrl+Shift+Enter, and it creates the perfect multiplication table.
Now it has to be square, you have to have 12 numbers down the side, 12 numbers across the top, or 10x10, or 5x5, or 50x50.
It won't work for 10 going across the top and 12 going down the side.
But a cool, cool trick.
So if you're interested in array formulas, check out Mike's new DVD, it's for sale right now at mrexcel.com . If you just go to the MrExcel store, it's there on the first page.
The book is in production right now, will probably be out in about 3 or 4 weeks, but the DVD is good to go and in stock.
Well hey, I want to thank you for stopping by, creating a multiplication table with MMULT, thanks to Mike Girvin for that hot trick from his new book, see you next time for another netcast from MrExcel!
