silly pivot table grouping question

[anationalacrobat]

I think I'm missing the easy way of doing this. I've worked out the long way already.

I have a range of pledge loss and refusals dumped from the database. Refusal for this year, dollar amount from year previous, and other info that's good to know. The simple pilot table right now is grouping everything by refusal type. So now I know I have 22 lost pledges due to death, 10 refusals, and so forth.

But now I need to group these pledges by dollar range. So these are the figures for $1 to $999, $1k to $9999, and $10k+.

The long way of doing this is adding a couple columns to the right of the dollar amount. First column checks to see if it's within the $1 to $999 range and prints "$1 to $999" if true, second column does "$1k to $9999", etc. Fourth column concatenates all three cells which should be value, blank and blank, blank, value, blank, etc. Then I should be able to group by that. Now theoretically I could put all of those tests in one cell with nested formula but I like splitting things out so I can catch errors more easily.

So that's my solution but I can't shake the feeling that I'm missing something here. It seems to me that this sort of grouping functionality should already be present in the pivot table and I'm just too thick to see it. But I've been wrong about that sort of thing before. I would have sworn up and down that there should be a native "distinct count" function in the pivot table that shouldn't require kludges in the data but, sadly, that's exactly what you need to do.

So, am I missing the obvious or is the kludge the only way to go?

 

[SkipVought]

Hi,

Here's how to group on the dollar amount field. CAVEAT: Every single value in the source data dollar amount column, MUST BE NUMERIC! NO TEXT or ERROR values!

In the PT, right-click the dollar amount field.

Select Group & show detail

Select Group

In the Grouping Window, change the By: value as needed.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Well it appears, after closer reading of your requirements, that you need a VARIABLE range.

Make a helper column Headed Grp using a formula to return "<$1k", "<$10k", ">=$10k"

Then drag Grp into the ROW or COLUMN AREA of your PT and the dollar amount field into the DATA AREA as SUM of....

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[anationalacrobat]

See, that's just it. I found the writeups online that said to do just that. I've done other reports where I've had to group by dates and that worked fine. But this one wasn't playing nice.

Doing it my long method, this is how it looks.

Deceased $0 349 $0.00
$1 to 999 136 $22,422.00
$1000 to 9999 42 $90,673.17
$10k+ 7 $161,066.13

And it continues on down the sheet for the different categories of refused and lost.

So while I have the results I need right now, if there's a better way I'd like to know of it. I was thinking that maybe my problem is I can't group by summed numbers. When I did my monthly grouping earlier, the original report had people down the vertical axis and the sessions they attended going across the right axis. Those dates were by month/day/year and so telling it to group by month did a small rollup. I thought maybe the dollars had to go across the horizontal axis (grasping at straws here) and that didn't work either.

I figured I'd see where I was going dumb with a nice example online and suddenly the error of my way would be plain to see. Not so. It may still be a dumb mistake I'm making or a misconception I'm contemplating but I've yet to see the way of correctness.

 

[SkipVought]

I set up a table like this with NAMED RANGES...
[tt]
GrpTblVal GrpTblLbl
0 $0
1 $1 to 999
1000 $1000 to 9999
10000 $10k+
[/tt]
Source data formula in column heaede Grp
[tt]
=INDEX(GrpTblLbl,MATCH(B2,GrpTblVal,1),1)
[/tt]
PT result...
[tt]
Sum of Amt
Grp Total
$0 $0
$1 to 999 $940
$1000 to 9999 $3,282
$10k+ $667,778
Grand Total $672,000
[/tt]
leading spaces to 'sort' Grp as needed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[anationalacrobat]

Ok, looks like I'll have to poke around with this some more.