Susan asks..."I have been using SUMPRODUCT a lot ever since you showed me how since I cant use SUMIFS for users with pre-2007 version. But, is there an AVERAGEPRODUCT or something similar? How about MAXPRODUCT?"
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Now today's question sent in by Susan, Susan had been in the Data Analyst Boot Camp, that's the seminar that Mike Alexander and I do twice a year.
Now the next one of these is coming up May 18th and 19th 2010 in Chicago.
Susan said “Hey, I've been using SUMPRODUCT ever since the boot camp, I love it.
I know that SUMIFS could do what I need to do, but I have to share this workbook with people at a customer site who's still using Excel 2003, so I can't use SUMIFS.” But she said “Alright, SUMPRODUCT is great, but I want ‘AVERAGEPRODUCT’ or ‘MAXPRODUCT’.
I want to do what AVERAGEIFS could do, or what, in theory, MAXIFS would do if there was such a thing.” And so, I'm going to build kind of a little formula here.
Now I'm going to start out with a formula, it's not going to work, I'm going to say =IF.
And I want to look through column A over here, and I'll press F4 to lock that down and say “Is that equal to this A here in column E?” So, that's equal, then what I want is, I want the corresponding number from column B, and again press F4 to lock that down.
If it's not equal, I'm going to put “”. Alright, now why “”? Well, the AVERAGE function is a clever function that will average all of the numeric values, but ignore text values.
So what's happening here is, any time that I get an A. I'm going to have that number, anytime that I have anything else, I'm going to get text.
And then if I could somehow average to this whole array of numbers, it will only average the ones that match(?).
Clever, huh?
Alright, so this formula that I have right here will never work, you have to use something called a wrapper function.
A wrapper function says “Hey, we're going to take that array of numbers and aggregate it somehow, either using SUM or AVERAGE or MAX or MIN.” So I'll use here AVERAGE().
Now, because that is an array, we have to do Ctrl+Shift, I hold down Ctrl+Shift and press Enter, and it does the math, when I copy that down it works beautifully.
So, those are the AVERAGE of all the cells.
Now if I was looking for the largest, that's one of things that Susan had mentioned, what we could use MAX instead of AVERAGE.
Again Ctrl+Shift+Enter, make sure you do just one of those at a time, MAX, Ctrl+Shift+Enter, and now copy that down to our other cells, and there's the largest value.
So even though SUMPRODUCT won't do it, you can use any kind of a wrapper function around that IF statement to check it out.
So I want to thank Susan for sending that question in, want to invite you down to the next Data Analyst Boot Camp.
You know, I think I'm the funniest Excel guy there is, but Mike Alexander is pretty funny too, and there's a lot of fun.
Couple of days out there, Mike half the day, me half the day, you'll learn a lot about Excel.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Now today's question sent in by Susan, Susan had been in the Data Analyst Boot Camp, that's the seminar that Mike Alexander and I do twice a year.
Now the next one of these is coming up May 18th and 19th 2010 in Chicago.
Susan said “Hey, I've been using SUMPRODUCT ever since the boot camp, I love it.
I know that SUMIFS could do what I need to do, but I have to share this workbook with people at a customer site who's still using Excel 2003, so I can't use SUMIFS.” But she said “Alright, SUMPRODUCT is great, but I want ‘AVERAGEPRODUCT’ or ‘MAXPRODUCT’.
I want to do what AVERAGEIFS could do, or what, in theory, MAXIFS would do if there was such a thing.” And so, I'm going to build kind of a little formula here.
Now I'm going to start out with a formula, it's not going to work, I'm going to say =IF.
And I want to look through column A over here, and I'll press F4 to lock that down and say “Is that equal to this A here in column E?” So, that's equal, then what I want is, I want the corresponding number from column B, and again press F4 to lock that down.
If it's not equal, I'm going to put “”. Alright, now why “”? Well, the AVERAGE function is a clever function that will average all of the numeric values, but ignore text values.
So what's happening here is, any time that I get an A. I'm going to have that number, anytime that I have anything else, I'm going to get text.
And then if I could somehow average to this whole array of numbers, it will only average the ones that match(?).
Clever, huh?
Alright, so this formula that I have right here will never work, you have to use something called a wrapper function.
A wrapper function says “Hey, we're going to take that array of numbers and aggregate it somehow, either using SUM or AVERAGE or MAX or MIN.” So I'll use here AVERAGE().
Now, because that is an array, we have to do Ctrl+Shift, I hold down Ctrl+Shift and press Enter, and it does the math, when I copy that down it works beautifully.
So, those are the AVERAGE of all the cells.
Now if I was looking for the largest, that's one of things that Susan had mentioned, what we could use MAX instead of AVERAGE.
Again Ctrl+Shift+Enter, make sure you do just one of those at a time, MAX, Ctrl+Shift+Enter, and now copy that down to our other cells, and there's the largest value.
So even though SUMPRODUCT won't do it, you can use any kind of a wrapper function around that IF statement to check it out.
So I want to thank Susan for sending that question in, want to invite you down to the next Data Analyst Boot Camp.
You know, I think I'm the funniest Excel guy there is, but Mike Alexander is pretty funny too, and there's a lot of fun.
Couple of days out there, Mike half the day, me half the day, you'll learn a lot about Excel.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
