Hi All,

I have a source of data formatted as below:

Retailer No. | Desc. | Value
004 | Total hours | 5000
004 | Retail hours | 2500
009 | Total hours | 6000
009 | Retail hours | 3000

I have a pivot table looking at this data source and I want to include a
field that performs the calculation '= [Retail hours]/[Total hours]'
unfortunately only the field 'Desc' is shown as a title in the pivot table to
I cant do calculate field as the data isnt setup that way.

Does anyone know a way to do what i need?

Many Thanks
--
Adam
-----------
Windows XP + Office Pro XP

Adam wrote:
Hi All,

I have a source of data formatted as below:

Retailer No. | Desc. | Value
004 | Total hours | 5000
004 | Retail hours | 2500
009 | Total hours | 6000
009 | Retail hours | 3000

I have a pivot table looking at this data source and I want to include a
field that performs the calculation '= [Retail hours]/[Total hours]'
unfortunately only the field 'Desc' is shown as a title in the pivot table to
I cant do calculate field as the data isnt setup that way.

Does anyone know a way to do what i need?

You need to reorganize your data
Retailer | TotalHr| RetailHr
004 | 5000 | 2500
009 | 6000 | 3000


If Total hours and Retail hours is all you have got, you can do an
autofilter and use that to first get all of the first (with the
retailers) then the second (with the retailers) and paste them next to
each other:

Retailer | value | Retailer|Value
004 | 5000 | 004 |2500
009 | 6000 | 009 |3000

Chech if the retails are the same on each line
Remove the second column retailer and rename the other columns

If this is possible will depend on how the rest of you data is put
together (and how much it is).

You could use conditional formatting to see if the first is the same as
the second. That should help you determine extremely fast if there are
differences or not on a glance.

houghi
--
At the source of every error which is blamed on the computer you will
find at least two human errors, including the error of blaming it on
the computer.

When you create your pivottable, are you dragging the Retailer number to the row
field and the desc to the column field and the Value to the data field?

Then can you add the calculated field?

Adam wrote:

Hi All,

I have a source of data formatted as below:

Retailer No. | Desc. | Value
004 | Total hours | 5000
004 | Retail hours | 2500
009 | Total hours | 6000
009 | Retail hours | 3000

I have a pivot table looking at this data source and I want to include a
field that performs the calculation '= [Retail hours]/[Total hours]'
unfortunately only the field 'Desc' is shown as a title in the pivot table to
I cant do calculate field as the data isnt setup that way.

Does anyone know a way to do what i need?

Many Thanks
--
Adam
-----------
Windows XP + Office Pro XP

--

Dave Peterson

Here is Dave's excellent suggestion
in pictures:
http://www.freefilehosting.net/download/3d59a