DAVERAGE criteria HELP!!!!

[Guest]

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

 

[Jim Cone]

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
http://www.realezsites.com/bus/primitivesoftware


"farmedgirl"
<[email protected]>
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

 

[Dave Peterson]

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:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

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

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

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx