software

Hi
I am using Excel XP version 2002. I have a database with 1000 different
farmer ID numbers, each with multiple rows of use rates for a chemical. I
would like to find the average use rate for each of these growers. I can
enter a formula: = DAVERAGE(database range, use rate column, criteria) But
the criteria part is the misery!I need to create 1000 criteria columns (colum
heading with each ID number) for each grower's ID #! I have tried one column
heading and all of the ID numbers below, but the average is then cummulative.
I would love to be able to create the formula, then copy and paste it down a
results row, supplying me with the average use rate for each grower, but the
criteria requires two cells (column name and criteria), and my pasted results
only a one cell difference. Is there any way to accomplish my goal of not
having to type out 1000 individual formulas? I hope this isnt too confusing -
it is hard to describe my problem without being able to show the excel sheet

Thanks in advance for any help or hints
-Kimberly

Kimberly,
It sound like the subtotals feature should work?
It will total, count, average or other at each change in text.
So if each row has an ID, it may do what you want.
Got to... Data | Subtotals
--
Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftwarequot;farmedgirlquot;
gt;
wrote in message
...
Hi
I am using Excel XP version 2002. I have a database with 1000 different
farmer ID numbers, each with multiple rows of use rates for a chemical. I
would like to find the average use rate for each of these growers. I can
enter a formula: = DAVERAGE(database range, use rate column, criteria) But
the criteria part is the misery!I need to create 1000 criteria columns (colum
heading with each ID number) for each grower's ID #! I have tried one column
heading and all of the ID numbers below, but the average is then cummulative.
I would love to be able to create the formula, then copy and paste it down a
results row, supplying me with the average use rate for each grower, but the
criteria requires two cells (column name and criteria), and my pasted results
only a one cell difference. Is there any way to accomplish my goal of not
having to type out 1000 individual formulas? I hope this isnt too confusing -
it is hard to describe my problem without being able to show the excel sheet

Thanks in advance for any help or hints
-Kimberly

You may want to consider using data|Pivottable, too.

Make sure your table of data has one header row.
Select the table (include the header row)
Data|Pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the Id header button to the row field
drag the rate header button to the column field
(double click on that button and change it to Average)

And finish up.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
office.microsoft.com/downloads/2000/XCrtPiv.aspx
office.microsoft.com/assistan...lconPT101.aspx

farmedgirl wrote:
gt;
gt; Hi
gt; I am using Excel XP version 2002. I have a database with 1000 different
gt; farmer ID numbers, each with multiple rows of use rates for a chemical. I
gt; would like to find the average use rate for each of these growers. I can
gt; enter a formula: = DAVERAGE(database range, use rate column, criteria) But
gt; the criteria part is the misery!I need to create 1000 criteria columns (colum
gt; heading with each ID number) for each grower's ID #! I have tried one column
gt; heading and all of the ID numbers below, but the average is then cummulative.
gt; I would love to be able to create the formula, then copy and paste it down a
gt; results row, supplying me with the average use rate for each grower, but the
gt; criteria requires two cells (column name and criteria), and my pasted results
gt; only a one cell difference. Is there any way to accomplish my goal of not
gt; having to type out 1000 individual formulas? I hope this isnt too confusing -
gt; it is hard to describe my problem without being able to show the excel sheet
gt;
gt; Thanks in advance for any help or hints
gt; -Kimberly

--

Dave Peterson