On Fri, 4 Nov 2005 09:07:43 -0600, cj21
wrote:


That is brilliant, but i have a more difficult problem:

ProductCode Tariff
01011100 3
01011900 40
01012000 40
01021000 3
01029010 7
01029020 7
01029090 40
01031000 3
01039100 40
01039200 40
01041010 3
01041090 40
01042010 3
01042090 40
01051110 3
01051190 40
01051900 40
01059100 40
01059900 40
01060010 3
01060020 7
01060030 7
01060090 40
02011000 40
02012000 40
02013000 40
02021000 40
02022000 40
02023000 40
02031100 60
02031200 60
02031900 60
02032100 60
02032200 60
02032900 60
02041000 40
02042100 40
02042200 40
02042300 40
02043000 40
02044100 40
02044200 40
02044300 40
02045000 40
02050000 40
02061000 40
02062100 40
02062200 40
02062900 40
02063000 40
02064100 60
02064900 60
02068000 60
02069000 60
02071000 40
02072100 40
02072200 40
02072300 40
02073100 40
02073910 40
02073990 40
02074100 40
02074200 40
02074300 40
02075000 40
02081010 15
02081090 60
02082000 60
02089010 15
02089090 60
02090000 60

Each product has a corresponding tariff (in a %). I want to find the
average tariff for the two digits.

So average tariff for product 01 would equal the sum of the tariff for
each line (that begins with 01), divided by the number of its
corresponding products. In this example the division for product
cattogry01 would be by: sum of tariffs/23.

I know how to work out the average by using the wizard, but i want a
nice formula that just fills down like in my last problem.


Chris

You can do this easily using a helper column along with either Pivot Table (my
preference) or SubTotals.

First set up a third helper column -- let's call it Type.

Formula: =INT(ProductCode/10^6)

That'll give you the first two digits of the Product Code.

For a Pivot Table, with the active cell someplace in the table, select
Data/PivotTable Finish

Drag Type to the Rows area
Drag Type to the Data area
Drag Tariff to the Data area

Right click on "sum of type"; Field Settings and select Count
Rename it as you wish and also select Number if you don't like the
default number format.

Right click on "sum of tariff"; Field Settings and select Average
Rename it as you wish and also select Number if you don't like the
default number format.

Finally, with the cursor in the Pivot Table, select from the top menu bar
Format/Auto Format and select an attractive format for your report.

=============================

For the Data/Subtotals wizard, you first have to ensure your data is sorted by
Type (i.e. first one or two digits).

Then select Data/Subtotals

At each change in Type
Use Function Count
Add Subtotal To (You can choose any field here; the difference will be
where it appears in the report)

Then again select Data/Subtotals
Ensure Replace Current Subtotals is DEselected

At each change in Type:
Use Function Average:
Add Subtotal to Tarriff

Then collapse the table using the buttons on the left side to display how you
want.

Rename the labels to your liking.


--ron