Pivot table grouping

S

Susan_GW

I'm trying to group columns in a pivot table according to

4-4.49
4.50 - 4.99
5.0 - 5.49
5.50 - 6

In the grouping box I enter 4 as my start at and 6 as my end, with .49 as
the By. Doing so causes the following column heads to appear:

4-4.49
4.49-4.98
4.98-5.47
5.47-5.96

I'm sure it's something obvious that I'm missing. Any ideas?

Much appreciated,

Susan
 
D

Debra Dalgleish

You should enter .5 as the By value, and the groups will be:

4-4.5
4.5-5
5-5.5
5.5-6
 
S

Susan_GW

Bob -

Thanks very much for your quick help. When I try this approach, I get the
following groupings:

4-4.49999999999
4.49999999999-4.99999999998
4.99999999998-5.49999999997
5.49999999997-5.99999999996

I think I'm still missing something obvious. This project is calling for the
groupings to be

4-4.49
4.5 - 4.99
5.0 - 5.49
5.5 - 5.00
 
S

Susan_GW

Debra - Thanks very much for responding so quickly. The project is calling
for the groupings to be as follows:

4-4.49
4.5 - 4.99
5.0 - 5.49
5.5 - 5.00

When I use .50, I get the following columns:

4 - 4.5
4.5 - 5
5 - 5.5
5.5 - 6

Any ideas?

Thanks again for your assistance.

Susan
 
S

Susan_GW

The last entry should be 5.5 - 6.00 (not 5.00).

Susan_GW said:
Bob -

Thanks very much for your quick help. When I try this approach, I get the
following groupings:

4-4.49999999999
4.49999999999-4.99999999998
4.99999999998-5.49999999997
5.49999999997-5.99999999996

I think I'm still missing something obvious. This project is calling for the
groupings to be

4-4.49
4.5 - 4.99
5.0 - 5.49
5.5 - 5.00
Click to expand...
 
R

Roger Govier

Hi Susan

I think the only way you will manage this is to add another column
called Groups to your source data.

Create a named range called Grouping with data as below
0 Under
3.99 4.0 - 4.49
4.49 4.5 - 4.99
4.99 5.0 - 5.49
5.49 5.5 - 5.99
5.99 Over


In your newly added column, enter the following formula
=VLOOKUP(B2,grouping,2)
where B2 is the cell containing the first of your scores. Copy Down.

Now, remove your field scores from the PT and drag Groups to the Column
area instead.
You may of course still need Scores as a Data field, depending upon what
your PT is trying to show.
 
Top