Hey, welcome back to Mr. Excel net cast. I’m Bill Jelen. A couple of days ago we talked about Jason's problem. He wants to show groups of percentages in a pivot table and there’s no sure way to format that correctly.
One solution is we can come out here to the right of the pivot table, and build a little table with the groups. So I’m aiming 5% and 5.5%, and I'm going to grab the fill handle and drag it down to far enough below our data. And then here I'm going to use that text functions to format that exactly as I wanted so equal text of the 5% and in puts I’m going to put 00.0% and present, and then I'll put a dash between the two percentages and present.
And then again, the text of the next value still with the 00.0 % and you see them again and answer their 05.0%/05.5% and when I copy this down we’re going to get all of the values found. And the reason I used 00 in the format is because I want it to sort correctly that the things with 10 should come after 09, and if we just use nine the nines would come to the end, so I’m going to add a new column here I’ll call this a group and we use equal view look up? It's not like the normal view look up though usually though what are you doing if you look up or become a false at the end. This is a range view look up, and this is the one case where we don’t put anything at the end. Don’t become a false basically defaulting to a true and it’s going to give us a range, so I’ll copy that down.
Alright, good, we’re there. And now when we create a pivot table insert, pivot table. Okay, I can take that field the group field to the real levels, and then used customer to get account of the customer. And see how many customers show up in each individual group. So if this does solve Jason's problem although really is sort of cheating because we’re not selling with the pivot table. We’re staying up here this is the weakness this is where pivot tables can't do what we want.
And so therefore, we’re going to go outside the pivot table, and basically back in the original data said create a nice view of how we want that formatted. So a couple of tricks here, first while I was using the text function in order to format a number as we want it formatted, and then using a view look up without kind of false at the end which basically says that anything between six up to 6.49999 is going to show up with 66.5%. And so interesting use of a view look up I almost always use the column false which is the exact match here's one instance where we found something where we could use, the true.
So interesting where to go, all right I want to thank you for stopping by well, see you next time for another net cast for Mr. Excel.
Transcription by:
Scribe4you Transcription Services