This is cool! Use "AverageIf" to get a Dynamic Floating Bracket!
Dave from Florida has a great little workbook to show off today! Dave tallies up annual physical exam statistics for area Fire Departments - which include pushups. In working with the stats, Dave can't reasonably compare a 20 year olds performance to a 49 year olds performance, so -using inherent functions of Excel - he found an interesting way to work with and present these statistics. Follow along with Bill in Episode #1585 to see Dave's method and the product of his work.
...This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Dave from Florida has a great little workbook to show off today! Dave tallies up annual physical exam statistics for area Fire Departments - which include pushups. In working with the stats, Dave can't reasonably compare a 20 year olds performance to a 49 year olds performance, so -using inherent functions of Excel - he found an interesting way to work with and present these statistics. Follow along with Bill in Episode #1585 to see Dave's method and the product of his work.
...This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast.
vs.Floating Bracket Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, a cool idea, sent in by Dave from one of the South Florida fire departments down there.
Dave is a frequent contributor to the MrExcel netcast.
And so Dave, is keeping track of statistics for all the firefighters on the force in large fire department.
I don't know actually how many people he has.
I created a data set here with two hundred and some firefighters.
And for each firefighter, they do an annual physical exam, where they see how many push-ups they can do.
All right, and then [ inaudiable ] Dave has all the firefighters, their age and the number of push-ups and they want to see how they do compared to the average.
But, there's 20 year old guys in the force, and there's 55 year old guys in the force.
And it's not fair to compare the 55 year old to the 20 year old.
And so Dave came up with a clever method.
He even said, you know, even if you compare based on decade. All right.
If you take a 41 year old and a 49 year old, you just can't, you can't compare those.
That's not a fair comparison.
So, he used one of the new functions in Excel 2007.
I hope this was really cool.
He used AVERAGEIFS.
AVERAGEIFS, and what he did is, he built a bracket.
That if this person is 20, they're compared to everyone who's 18, 19, 20, 21 or 22.
This person who's 49, they're compared to everyone who is 47 through 51.
All right so, it's comparing, you know, yes, I think the average.
Let's see what's the average of everybody.
The average of everybody is 35 push-ups.
But, you take this person who's 49, and you compare them to the average of everybody, he'd be doing horribly.
I've compared them to the average of everyone who's 47 to 51.
He's still 20% under the average.
But he's not, you know, 75% below the average. All right.
So, let's take a look at this formula.
That was pretty cool. I used evaluate formula to take a look at it.
And the first thing it does, is it takes his current age in B2 and subtracts this constant from it.
So, B2-G2.
Let's evaluate. B2 is 20.
G2 is 2 and so we now have built on the fly, a criteria of greater than or equal to 18 and see that actually evaluates the text.
All right. So, we're looking at all of the push-ups in C2 to C269 and we're going to average them, if it matches the criteria, where the age is greater than or equal to 18.
All right, now evaluate again. So now, we have B2, that's 20+2 and it is less than or equal to 22.
So, right at this point, it's going to take an average of all the people who are 18 through 22.
And that'll be the average, by which this firefighter is compared, all right.
But if we go down to someone else.
Let's find someone older here, so someone is 57.
When we evaluate the formula for him, we're looking at everyone greater than or equal to 55.
less than or equal to 59.
All right so, it actually creates a dynamic floating bracket, looking at people 2 years younger and 2 years older all the way down, and seeing how you compare against that particular group.
And so it's possible, that someone who's 57 and someone who's 56 are going to have a different bracket average.
And so even though, they're kind of in the bracket together there.
This guy is being compared to someone who's 54.
This guys want to be in comparison when it's 55.
And so, it creates an interesting, interesting set of statistics.
I thought that was a cool idea So, shout out to Dave and everyone in his fire department for sending in this idea.
All right hey, I want to thank you for stopping by.
Will see you next time, for another netcast from MrExcel.
Learn Excel from MrExcel podcast.
vs.Floating Bracket Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today, a cool idea, sent in by Dave from one of the South Florida fire departments down there.
Dave is a frequent contributor to the MrExcel netcast.
And so Dave, is keeping track of statistics for all the firefighters on the force in large fire department.
I don't know actually how many people he has.
I created a data set here with two hundred and some firefighters.
And for each firefighter, they do an annual physical exam, where they see how many push-ups they can do.
All right, and then [ inaudiable ] Dave has all the firefighters, their age and the number of push-ups and they want to see how they do compared to the average.
But, there's 20 year old guys in the force, and there's 55 year old guys in the force.
And it's not fair to compare the 55 year old to the 20 year old.
And so Dave came up with a clever method.
He even said, you know, even if you compare based on decade. All right.
If you take a 41 year old and a 49 year old, you just can't, you can't compare those.
That's not a fair comparison.
So, he used one of the new functions in Excel 2007.
I hope this was really cool.
He used AVERAGEIFS.
AVERAGEIFS, and what he did is, he built a bracket.
That if this person is 20, they're compared to everyone who's 18, 19, 20, 21 or 22.
This person who's 49, they're compared to everyone who is 47 through 51.
All right so, it's comparing, you know, yes, I think the average.
Let's see what's the average of everybody.
The average of everybody is 35 push-ups.
But, you take this person who's 49, and you compare them to the average of everybody, he'd be doing horribly.
I've compared them to the average of everyone who's 47 to 51.
He's still 20% under the average.
But he's not, you know, 75% below the average. All right.
So, let's take a look at this formula.
That was pretty cool. I used evaluate formula to take a look at it.
And the first thing it does, is it takes his current age in B2 and subtracts this constant from it.
So, B2-G2.
Let's evaluate. B2 is 20.
G2 is 2 and so we now have built on the fly, a criteria of greater than or equal to 18 and see that actually evaluates the text.
All right. So, we're looking at all of the push-ups in C2 to C269 and we're going to average them, if it matches the criteria, where the age is greater than or equal to 18.
All right, now evaluate again. So now, we have B2, that's 20+2 and it is less than or equal to 22.
So, right at this point, it's going to take an average of all the people who are 18 through 22.
And that'll be the average, by which this firefighter is compared, all right.
But if we go down to someone else.
Let's find someone older here, so someone is 57.
When we evaluate the formula for him, we're looking at everyone greater than or equal to 55.
less than or equal to 59.
All right so, it actually creates a dynamic floating bracket, looking at people 2 years younger and 2 years older all the way down, and seeing how you compare against that particular group.
And so it's possible, that someone who's 57 and someone who's 56 are going to have a different bracket average.
And so even though, they're kind of in the bracket together there.
This guy is being compared to someone who's 54.
This guys want to be in comparison when it's 55.
And so, it creates an interesting, interesting set of statistics.
I thought that was a cool idea So, shout out to Dave and everyone in his fire department for sending in this idea.
All right hey, I want to thank you for stopping by.
Will see you next time, for another netcast from MrExcel.
