Database question

K

K. Georgiadis

I have a pretty big data list organized as follows:

Across the top: names of states (21 in all)
Column A: years (from 1965 to 2003)
Columns B through U, acres planted for each state by year

I want to sort the data for the top 5 states (in terms of
AVERAGE acres planted) during the last 3 years. Is there
a "smart formula" I can use or do I have to go through a
repetitive process of sorting?
 
F

Frank Kabel

Hi
could you change the layout of your table. e.g.
row 1: all years
column A: all states

Then you could add a helper column. Lets say in column X with the
formula
=AVERAGE(U2:W2)
and sort with this helper column (U:W are your last three years)
 
F

Frank Kabel

Hi
how do you want to sort the data? and what Excel version are you suing.
In later versions you can also sort by rows instead of columns
 
K

K. Georgiadis

I use Excel 2002. I have used row sorting before but that
was an instance where it was OK to move the data to a
different column. In this instance the data are lined up
under the names of the states and the column headings
would have to move together with the data.

To give you a flavor of the setup:

States (21 total) AL AZ AR CA etc
Years:
1965
1966
1967 etc
all the way to 2003

In the intersects are the hectares planted.

I'm looking to sort the data for the last three rows
(2001, 2002, 2003), determine an average for each of the
21 states, and displaying the 5 states with the highest
average.

A tall order? :)
 
K

K. Georgiadis

I solved this by
1) copying the column headings to a new row below the
table
2) calculating an average of the last 3 years
3) sorting by row (I use Excel 2002). Once I took the
time to read properly the online instructions, I was able
to move the headings together with the related data.

Moral of the story: never miss a chance to read an
instruction manual.

Thanks for putting me on the right track
 
Top