Today, Bill "MrExcel" Jelen and Mike "ExcelisFun" Girvin look at a question that asks: "How can I audit 70% of the book value, with samples based on the value?" Conditional Formatting produces 70% of the cells and not 70% of the value. Using a previous Dueling Excel Episode, Bill attempts Goal Seel, but that doesn't really get the result either. Mike, on the other hand goes in another direction based on some of the work Bill has set up. Watch the Duel, learn the method, get the result with Episode #1454.
...This blog 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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog 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!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey, welcome back. It's another Dueling Excel podcast. I'm Bill Jelen from MrExcel.
We’ll be joined by Mike Girvin from Excel is Fun.
Today's question: top 70%.
This is episode 88.
All right, so here's the question: someone wants to audit 70% of the book value and the sample is based on the values.
So, he's looking for enough records to get him the top 70%, kind of like this.
My initial reaction is, hey, well, let's just try that brand-new conditional formatting that they have there.
We go into home, conditional formatting, highlight cells-- oh, no, top bottom rules and we can say top 10%.
I hope that I can change this to be 70% percent, but what that does is that does 70% of the cells, not 70% of the value.
So, that's not going to work.
All right, so, I took a page out of Mike's formula book here.
Check out this formula.
This is cool.
I learned this from watching Mike on the Dueling Excel podcast.
I want to take the LARGE of this range and I want to take the largest 1 2 3 4-- I’m not sure how many values.
So, I say the row of the INDIRECT of A1 to A whatever's in this.
So, right now, it’s saying A1 to A3.
A1 to A3.
So, that's going to give me the numbers 1 2 3 and array and then the LARGE is going to give me the first, second, third largest values in that range and then finally the SUM.
You have to do Ctrl Shift Enter to get all that in.
What it does is if I ask for the largest one value, well that's 2592.
Simple.
If I ask for the largest two values, that's 4320, largest three values, largest four values, largest five values, and so on.
I can get those values.
Then, what I’m doing is I’m actually using the LARGE here, asking for the fifth largest value using conditional formatting here to say that if the value in that particular cell is greater than the limit, D21, then highlight it.
All right, so, it's all working kind of good except for I have to do this horrible manual goal seek.
Is the first number 70%?
No.
Are the first two numbers 70%?
No.
Are the first three numbers-- there we go.
All right, so then I can stop.
So, I'm going to find myself plugging in 1 2 3 4 5.
Goal seek, unfortunately, will not, will not solve this because, of course, 17 is larger than 70%.
So, if we try to goal seek this into true, it's just not going to find a value.
Mike, do you have a better solution for this?
Mike: Thanks, MrExcel.
Yes, you know, that is pretty awesome there.
This is kind of a hard one because there is no built-in feature that I can think of that would do the values, right?
There's things that take the top 10 or the top 5 or whatever, but usually, that's based on percentile and that's position, not cumulative total.
So, I really love this set up here because it will peg all three values.
All right, let's come over here.
The only way I can do this, like I said, it's to act-- either do something like MrExcel did with LARGE and that beautiful array formula or I'm just going to add a cumulative total, but before I can do cumulative total, you have to sort.
So, I'm going to field name at the top, data here, right click, sort-- in 2007 and 10, you can do the right-click.
I'm going to Z to A. Then I’m going to put a total at the bottom, Alt equals and add a cumulative column.
Now, I need to add as I'm going down and always compare the running total to this.
The running total is SUM of this, colon, close parentheses and then lock this cell reference here with the F4 key, but not this one.
That means it will have an expandable range as it goes down.
All right, so that's the running total and now I can simply edit this, F2, and divide this.
That's a running total.
This is the running total percentage.
Divide that by the total, F4, Ctrl Enter to populate.
Now, a pivot table could do this.
I think-- here's a pivot table right here.
So, you could get that with a pivot table.
In 2010, they have not only the cumulative, but-- not only the running total, but the percentage running total, which is what this is.
All right, so, then with that I'm going to notice here that by hand, I mean, really, that's-- that's how I do it.
I go, okay, there's the 70% mark and then just go audit those, right?
So, it's sorted and added an extra column to tell me cumulative.
If you wanted to highlight it, you know, I mean, at this point it'd be pretty easy, but percentage is 0.7.
I'm going to do a LOOKUP.
I'm going to notice some-- I could actually build a true/false formula like this.
Hey, is that greater than and then F4.
Notice the first time we get a true, that's the-- this is the hurdle value.
This is the value that MrExcel got using the LARGE and that value here, but I'm going to notice that this column here, I'm just asking: is each one of these greater than the 70?
I could do-- simulate this in an array formula, put it in INDEX and look this value up.
So, I’m going to try that.
I’m not going to use this.
That is there to illustrate.
Now, there are lots of repeats, but we'll deal with that.
So, this will be the LOOKUP-- if only I could type.
Let's just start with the array.
I could go like this, yes, that one because I added that column and say, greater than this.
What is this going to give me?
F9, the exact same thing we see here.
We're simulating this column here in the formula, Ctrl Z.
Notice one, two, third.
So, it's the third value, which is the first true, Ctrl Z.
I'm going to use the MATCH function.
The MATCH function-- if only I could type and I’m going to look up the number 1 comma.
Now, right, I'm sorry, not the number 1.
I'm going to look up the number true, I mean, the logical value true.
Remember this is giving us trues and falses.
Well, check this out.
MATCH, if you go comma 0 for exact since there's duplicates, it will only look up and return the first one.
So, right now, and this is an array formula, that little bit right there needs Ctrl Shift Enter so it'll-- gives me the third value, which is right there.
Now, I can just wrap that inside of INDEX and look up the values here, comma Ctrl Shift Enter.
So, the curly brackets when I Ctrl Shift Enter are in and now I can simply do the same formula MrExcel had.
Alt OD for conditional formatting, new rule, I’m going to say, anytime and notice I’ve highlighted these cells in advance.
The active cell is-- you have to build the formula from the point of view of that cell.
So, I’m going to say, hey, that.
I need to unlock it, so I’ll hit F4.
Anytime that's greater than or equal to that, and then format it.
So, I’m going to cut off.
All right, and if we were to change this, right, so we could then expand.
So, that would be the only way of, you know, building a formula like this, but again, once you build this cumulative column here, you can I it and then audit these.
All right, I’ll throw it back to MrExcel.
Bill: When you started with LOOKUP up row one I thought you're going to knock out that old, old, old =LOOKUP function, but no.
Good array formula there; MATCH in INDEX.
All right, I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and Excel is Fun.
We’ll be joined by Mike Girvin from Excel is Fun.
Today's question: top 70%.
This is episode 88.
All right, so here's the question: someone wants to audit 70% of the book value and the sample is based on the values.
So, he's looking for enough records to get him the top 70%, kind of like this.
My initial reaction is, hey, well, let's just try that brand-new conditional formatting that they have there.
We go into home, conditional formatting, highlight cells-- oh, no, top bottom rules and we can say top 10%.
I hope that I can change this to be 70% percent, but what that does is that does 70% of the cells, not 70% of the value.
So, that's not going to work.
All right, so, I took a page out of Mike's formula book here.
Check out this formula.
This is cool.
I learned this from watching Mike on the Dueling Excel podcast.
I want to take the LARGE of this range and I want to take the largest 1 2 3 4-- I’m not sure how many values.
So, I say the row of the INDIRECT of A1 to A whatever's in this.
So, right now, it’s saying A1 to A3.
A1 to A3.
So, that's going to give me the numbers 1 2 3 and array and then the LARGE is going to give me the first, second, third largest values in that range and then finally the SUM.
You have to do Ctrl Shift Enter to get all that in.
What it does is if I ask for the largest one value, well that's 2592.
Simple.
If I ask for the largest two values, that's 4320, largest three values, largest four values, largest five values, and so on.
I can get those values.
Then, what I’m doing is I’m actually using the LARGE here, asking for the fifth largest value using conditional formatting here to say that if the value in that particular cell is greater than the limit, D21, then highlight it.
All right, so, it's all working kind of good except for I have to do this horrible manual goal seek.
Is the first number 70%?
No.
Are the first two numbers 70%?
No.
Are the first three numbers-- there we go.
All right, so then I can stop.
So, I'm going to find myself plugging in 1 2 3 4 5.
Goal seek, unfortunately, will not, will not solve this because, of course, 17 is larger than 70%.
So, if we try to goal seek this into true, it's just not going to find a value.
Mike, do you have a better solution for this?
Mike: Thanks, MrExcel.
Yes, you know, that is pretty awesome there.
This is kind of a hard one because there is no built-in feature that I can think of that would do the values, right?
There's things that take the top 10 or the top 5 or whatever, but usually, that's based on percentile and that's position, not cumulative total.
So, I really love this set up here because it will peg all three values.
All right, let's come over here.
The only way I can do this, like I said, it's to act-- either do something like MrExcel did with LARGE and that beautiful array formula or I'm just going to add a cumulative total, but before I can do cumulative total, you have to sort.
So, I'm going to field name at the top, data here, right click, sort-- in 2007 and 10, you can do the right-click.
I'm going to Z to A. Then I’m going to put a total at the bottom, Alt equals and add a cumulative column.
Now, I need to add as I'm going down and always compare the running total to this.
The running total is SUM of this, colon, close parentheses and then lock this cell reference here with the F4 key, but not this one.
That means it will have an expandable range as it goes down.
All right, so that's the running total and now I can simply edit this, F2, and divide this.
That's a running total.
This is the running total percentage.
Divide that by the total, F4, Ctrl Enter to populate.
Now, a pivot table could do this.
I think-- here's a pivot table right here.
So, you could get that with a pivot table.
In 2010, they have not only the cumulative, but-- not only the running total, but the percentage running total, which is what this is.
All right, so, then with that I'm going to notice here that by hand, I mean, really, that's-- that's how I do it.
I go, okay, there's the 70% mark and then just go audit those, right?
So, it's sorted and added an extra column to tell me cumulative.
If you wanted to highlight it, you know, I mean, at this point it'd be pretty easy, but percentage is 0.7.
I'm going to do a LOOKUP.
I'm going to notice some-- I could actually build a true/false formula like this.
Hey, is that greater than and then F4.
Notice the first time we get a true, that's the-- this is the hurdle value.
This is the value that MrExcel got using the LARGE and that value here, but I'm going to notice that this column here, I'm just asking: is each one of these greater than the 70?
I could do-- simulate this in an array formula, put it in INDEX and look this value up.
So, I’m going to try that.
I’m not going to use this.
That is there to illustrate.
Now, there are lots of repeats, but we'll deal with that.
So, this will be the LOOKUP-- if only I could type.
Let's just start with the array.
I could go like this, yes, that one because I added that column and say, greater than this.
What is this going to give me?
F9, the exact same thing we see here.
We're simulating this column here in the formula, Ctrl Z.
Notice one, two, third.
So, it's the third value, which is the first true, Ctrl Z.
I'm going to use the MATCH function.
The MATCH function-- if only I could type and I’m going to look up the number 1 comma.
Now, right, I'm sorry, not the number 1.
I'm going to look up the number true, I mean, the logical value true.
Remember this is giving us trues and falses.
Well, check this out.
MATCH, if you go comma 0 for exact since there's duplicates, it will only look up and return the first one.
So, right now, and this is an array formula, that little bit right there needs Ctrl Shift Enter so it'll-- gives me the third value, which is right there.
Now, I can just wrap that inside of INDEX and look up the values here, comma Ctrl Shift Enter.
So, the curly brackets when I Ctrl Shift Enter are in and now I can simply do the same formula MrExcel had.
Alt OD for conditional formatting, new rule, I’m going to say, anytime and notice I’ve highlighted these cells in advance.
The active cell is-- you have to build the formula from the point of view of that cell.
So, I’m going to say, hey, that.
I need to unlock it, so I’ll hit F4.
Anytime that's greater than or equal to that, and then format it.
So, I’m going to cut off.
All right, and if we were to change this, right, so we could then expand.
So, that would be the only way of, you know, building a formula like this, but again, once you build this cumulative column here, you can I it and then audit these.
All right, I’ll throw it back to MrExcel.
Bill: When you started with LOOKUP up row one I thought you're going to knock out that old, old, old =LOOKUP function, but no.
Good array formula there; MATCH in INDEX.
All right, I want to thank everyone for stopping by.
We'll see you next time for another Dueling Excel podcast from MrExcel and Excel is Fun.
