I have data in the following format:

Column A is a numerical range from a2-a70
Row 1 is a numerical range of data from b1 - cc1

The data in between "row 1" and "column a" is the recorded data I need
to access.

(This data is the recording of energy directed at a cellphone antenna
and Column A is the 'angle' at which the energy strikes the widget,
with row 1 being the amount of energy fired at the widget. The result
is shown in the corresponding cell.)
(Column A) (B) (C) (D) (E) (F)
angle/energy 1 2 3 4 5
-3 (results in the intersection of each cell)
-2
-1
1
2
3

I want to be able to do varying analyses of the data such as:
1) For energy levels from 2 - 4, what is the minimum, maximum and
average result for all angles, or ranges of angles?
2) For a given energy level what is the maximum, minimum result, and
with what angle does that correspond?


It appears to me that pivot tables would be the best way to do this,
however with the data in such a table it becomes cumbersome; since I
have dozens of columns as I understand it to put those in a Pivot table
would require manually dragging each column heading into my table.

I thought that if I could arrange the data into three columns as
follow, the pivot table would do all that I would need:

(Column A) (Column B) (Column C)
angle / energy / measured result
-3 1 (result from the
intersection of each cell)
-2 1
-1 1
1 1
2 1
3 1
-3 2
-2 2
-1 2
1 2
2 2
3 2
etc..

I have manually copied and transposed a few rows to columns to try it
on a pivot table, and such a table with 3 columns seems to suit my
needs. It appears to me that there might be a way with the OFFSET
function to copy the data from a "table" format to a "column" format,
and I am open to all advice.


--
Ron H
------------------------------------------------------------------------
Ron H's Profile: http://www.excelforum.com/member.php...fo&userid=9749
View this thread: http://www.excelforum.com/showthread...hreadid=391591