Mystery Solved, II: A Crackerjack Sequel

8 Oct

Didn’t I tell you Debra Dalgleish is a pivot table expert? You’ll recall I prevailed upon her expertise, in last week’s nail-biter of an installment about grouped pivot table data whose tousled decimal points would not be rounded.

And you’ll further recall my self-satisfying workaround: importing a faux, zero-bearing record into the data set, a supplement that convinced the numbers above it to submit to a rounding at last.

That patch did what it set out to do, but at a cost: the empty zero could bias record counts, averages, minimums and the like, and so my Excalibur exhibits a bit of rust.

And that’s where Debra Dalgleish comes in. In an email communique beamed all the way from Canada she reported this spot-on resolution:

Once the grouping sequence is set in motion and you get here, e.g.

round1

Once there now simply alter the Starting at number to the degree of precision you need, for example:

round2

Or eliminate all the decimals if you wish. Then click OK. The grouping commences, and hews to your decimal significance:

round3

Tres cool. Note that the tweak need only recalibrate the Starting at field, and happily swerves past the Ending at junction. All due kudos to Debra for the superior tack, one which decisively obviates the need to contrive my counterfeit zero record, and which smuggles a meaningful round-off into the grouping process at just the right point.

That last allusion reminds us about the essential, petrified character of grouped pivot table numbers. They’re typically emplaced in the Row Labels area because they’re…labels, in spite of their indubitably numeric inception. After all, a grouped figure such as

3000-3500

can’t really be anything but. We would normally hold this truth to be of the self-evident character kind – namely, that a hyphenated phone number is not a quantitative thing – but again, because grouped numbers are numbers one needs to appreciate the loss in translation once a grouping is thrust upon them.

Of course again if the data happen to boast a zero or two, the data will group nicely all by themselves as a matter of course, and as such won’t need to call up the Starting At intervention. But Debra Dalgliesh’s tip should work all the time.

And it’s holiday time for me again. Sorry – I’ve been working nights to earn this vacation.

Advertisements

One Response to “Mystery Solved, II: A Crackerjack Sequel”

Trackbacks/Pingbacks

  1. Excel Roundup 20141013 « Contextures Blog - October 13, 2014

    […] you read Abbott Katz's previous article about pivot table grouping problems, you'll be interested in the follow-up […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: