Hi all,

I have a column called IncomeRange. I need to display the value in ranges
like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to
display as ranges? Is this an option or do I need to do some kind of formulas
or macros?

Thanks for all help

Just wanted to explain a little more.

Hello,

My requirement is to create a pivot report in which a column called
indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc.

The source of data for the pivot is a SQL Server query. I am not sure where
or how can I do it, so I am posting this in both groups.

Is it possible to take a value in a column and calculate and then return
multiple columns for multiple ranges?

example

baseCol values 0-9 10-20 21-30 31-40 etc
15 1
20 1
6 1
33 1
9 1
28 1

Thanks for any help and suggestions

"NetNewbie" wrote:

> Hi all,
>
> I have a column called IncomeRange. I need to display the value in ranges
> like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to
> display as ranges? Is this an option or do I need to do some kind of formulas
> or macros?
>
> Thanks for all help

Hi Netnewbie

What you need is simple - just use your pivot table's grouping feature.

Try this:

1. From inside your Pivot Table report, right click on any of the values in
the IncomeRange field.

2. Click Group and Show Detail, then click Group.

3. The Grouping dialog box appears. From here, notice these amounts will
pre-populate with default "Starting at", "Ending at", and "By" values, where
"Starting at" will = the minimum value in your IncomeRange field, and
"Ending at" will = the maximum value in your IncomeRange field, and
"By" represents the desired increment of the groupings

4. Modify the values as needed, or click OK to accept the defaults.

Note these groupings will update along with your data, the same way the rest
of the pivot table does



------
XSzil
Spreadsheet Heroine


"NetNewbie" wrote:

> Just wanted to explain a little more.
>
> Hello,
>
> My requirement is to create a pivot report in which a column called
> indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc.
>
> The source of data for the pivot is a SQL Server query. I am not sure where
> or how can I do it, so I am posting this in both groups.
>
> Is it possible to take a value in a column and calculate and then return
> multiple columns for multiple ranges?
>
> example
>
> baseCol values 0-9 10-20 21-30 31-40 etc
> 15 1
> 20 1
> 6 1
> 33 1
> 9 1
> 28 1
>
> Thanks for any help and suggestions
>
> "NetNewbie" wrote:
>
> > Hi all,
> >
> > I have a column called IncomeRange. I need to display the value in ranges
> > like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to
> > display as ranges? Is this an option or do I need to do some kind of formulas
> > or macros?
> >
> > Thanks for all help

Thank you for your response. It does not necessarily work in my case, since
the goruping option is only available if the filed is in the RowLabels
section. I needs them as individual columns in the values section.

I did it using the SQL query.

"x szil" wrote:

> Hi Netnewbie
>
> What you need is simple - just use your pivot table's grouping feature.
>
> Try this:
>
> 1. From inside your Pivot Table report, right click on any of the values in
> the IncomeRange field.
>
> 2. Click Group and Show Detail, then click Group.
>
> 3. The Grouping dialog box appears. From here, notice these amounts will
> pre-populate with default "Starting at", "Ending at", and "By" values, where
> "Starting at" will = the minimum value in your IncomeRange field, and
> "Ending at" will = the maximum value in your IncomeRange field, and
> "By" represents the desired increment of the groupings
>
> 4. Modify the values as needed, or click OK to accept the defaults.
>
> Note these groupings will update along with your data, the same way the rest
> of the pivot table does
>
>
>
> ------
> XSzil
> Spreadsheet Heroine
>
>
> "NetNewbie" wrote:
>
> > Just wanted to explain a little more.
> >
> > Hello,
> >
> > My requirement is to create a pivot report in which a column called
> > indexrange is to be displayed as n number of intervals eg: 0-9, 10-19 etc.
> >
> > The source of data for the pivot is a SQL Server query. I am not sure where
> > or how can I do it, so I am posting this in both groups.
> >
> > Is it possible to take a value in a column and calculate and then return
> > multiple columns for multiple ranges?
> >
> > example
> >
> > baseCol values 0-9 10-20 21-30 31-40 etc
> > 15 1
> > 20 1
> > 6 1
> > 33 1
> > 9 1
> > 28 1
> >
> > Thanks for any help and suggestions
> >
> > "NetNewbie" wrote:
> >
> > > Hi all,
> > >
> > > I have a column called IncomeRange. I need to display the value in ranges
> > > like 0 - 100, 101-200 etc., How can I convert a column in a pivot report to
> > > display as ranges? Is this an option or do I need to do some kind of formulas
> > > or macros?
> > >
> > > Thanks for all help