Mystery Solved, II: A Crackerjack Sequel

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.

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

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

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.