Pivot table grouping

[Guest]

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

 

[Bob Umlas]

try using .49999999999

 

[Bob Umlas]

try using .49999999999

 

[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

 

[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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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

 

[Guest]

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

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

 

[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.

 

[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.