Data in table, may need to convert to columns with OFFSET?

[Ron H]

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.

[Biff]

Hi!

These sound like fairly straightforward calculations and the setup you have
now is just fine.

> 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?

Do you need help with formulas to do these calcs or are you set in that you
want to use a pivot table and are just looking for help setting that up?

I can help with formulas. Can't help with the pivot table. Personally, I
hate 'em!

Biff

"Ron H" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ron H]

If it can be done without Pivot Tables I would gladly accept help with formulas.
I just thought that Pivot Tables would be the quickest way to check the values within a range, by using the grouping function.

[Biff]

Hi!

OK.....

These formulas all use references based on the size of the sample you
posted. A2:A7 for the angles and B1:F1 for the energies. All you need to do
is change the references to suit your actual table.

First thing.....

Give the "data" section of your table a name.

Select the range B2:CC70

In the Name Box type in something like Tbl. The name box is the little box
at the far left side of the formula bar. It shows what cell is currently
selected. Just click inside that box and type Tbl.

Now, you need 4 cells to hold the variables that you want to use for the
calcs. In these examples I'll use:

Energy
A10
A11

Angle
A14
A15

So, if you wanted to find the MIN, MAX or AVG for energies 2 to 4 for all
angles:

A10 = 2
A11 = 4

Formulas entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))

=MAX(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))

=AVERAGE(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))

If you wanted to find the MIN, MAX or AVG for energies 2 to 4 for angles -3
to 2:

A10 = 2
A11 = 4
A14 = -3
A15 = 2

Formulas array entered:

=MIN(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))

=MAX(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))

=AVERAGE(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))

If you want the corresponding angle for the MIN or MAX of any SINGLE energy:
For example, energy 3:

A10 = 3

You would probably want to use a different cell to hold this variable but I
just used A10 as the example.

Normally entered:

=INDEX(A2:A7,MATCH(MIN(INDEX(Tbl,,A10)),INDEX(Tbl,,A10),0))

=INDEX(A2:A7,MATCH(MAX(INDEX(Tbl,,A10)),INDEX(Tbl,,A10),0))

If you'd like a sample file to study this I'll be glad to put something
together. Just let me know how to contact you.

Biff

"Ron H" <[email protected]> wrote in
message news:[email protected]...
>
> If it can be done without Pivot Tables I would gladly accept help with
> formulas.
> I just thought that Pivot Tables would be the quickest way to check the
> values within a range, by using the grouping function.
>
>
> --
> 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
>

[Ron H]

Biff,
Very elegant. Thank you. I should have known that array formulae were the way to go. Thanks for everything!

I had come across another way late last night. On John Walkenbach's site he has a tip that is essentially a "reverse pivot" whereby you create a database table from a summary table. http://j-walk.com/ss/excel/usertips/tip068.htm

I used that to then create a pivot table from my data to find max, min, and average. But to do so, I need to group data along the x or y axis and then select which intervals I want included in the pivot table. Your way is more elegant, simple, and direct, and makes one use one's mind!

Hi!

OK.....

These formulas all use references based on the size of the sample you
posted. A2:A7 for the angles and B1:F1 for the energies. All you need to do
is change the references to suit your actual table.

First thing.....

Give the "data" section of your table a name.

Select the range B2:CC70

In the Name Box type in something like Tbl. The name box is the little box
at the far left side of the formula bar. It shows what cell is currently
selected. Just click inside that box and type Tbl.

Now, you need 4 cells to hold the variables that you want to use for the
calcs. In these examples I'll use:

Energy
A10
A11

Angle
A14
A15

So, if you wanted to find the MIN, MAX or AVG for energies 2 to 4 for all
angles:

A10 = 2
A11 = 4

Formulas entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))

=MAX(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))

=AVERAGE(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))

If you wanted to find the MIN, MAX or AVG for energies 2 to 4 for angles -3
to 2:

A10 = 2
A11 = 4
A14 = -3
A15 = 2

Formulas array entered:

=MIN(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))

=MAX(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))

=AVERAGE(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))

If you want the corresponding angle for the MIN or MAX of any SINGLE energy:
For example, energy 3:

A10 = 3

You would probably want to use a different cell to hold this variable but I
just used A10 as the example.

Normally entered:

=INDEX(A2:A7,MATCH(MIN(INDEX(Tbl,,A10)),INDEX(Tbl,,A10),0))

=INDEX(A2:A7,MATCH(MAX(INDEX(Tbl,,A10)),INDEX(Tbl,,A10),0))

If you'd like a sample file to study this I'll be glad to put something
together. Just let me know how to contact you.

Biff

"Ron H" <[email protected]> wrote in
message news:[email protected]...
>
> If it can be done without Pivot Tables I would gladly accept help with
> formulas.
> I just thought that Pivot Tables would be the quickest way to check the
> values within a range, by using the grouping function.
>
>
> --
> 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
>

[Biff]

Glad to help. Thanks for the feedback!

Biff

"Ron H" <[email protected]> wrote in
message news:[email protected]...
>
> Biff,
> Very elegant. Thank you. I should have known that array formulae were
> the way to go. Thanks for everything!
>
> I had come across another way late last night. On John Walkenbach's
> site he has a tip that is essentially a "reverse pivot" whereby you
> create a database table from a summary table.
> http://j-walk.com/ss/excel/usertips/tip068.htm
>
> I used that to then create a pivot table from my data to find max, min,
> and average. But to do so, I need to group data along the x or y axis
> and then select which intervals I want included in the pivot table.
> Your way is more elegant, simple, and direct, and makes one use one's
> mind!
>
> Biff Wrote:
>> Hi!
>>
>> OK.....
>>
>> These formulas all use references based on the size of the sample you
>> posted. A2:A7 for the angles and B1:F1 for the energies. All you need
>> to do
>> is change the references to suit your actual table.
>>
>> First thing.....
>>
>> Give the "data" section of your table a name.
>>
>> Select the range B2:CC70
>>
>> In the Name Box type in something like Tbl. The name box is the little
>> box
>> at the far left side of the formula bar. It shows what cell is
>> currently
>> selected. Just click inside that box and type Tbl.
>>
>> Now, you need 4 cells to hold the variables that you want to use for
>> the
>> calcs. In these examples I'll use:
>>
>> Energy
>> A10
>> A11
>>
>> Angle
>> A14
>> A15
>>
>> So, if you wanted to find the MIN, MAX or AVG for energies 2 to 4 for
>> all
>> angles:
>>
>> A10 = 2
>> A11 = 4
>>
>> Formulas entered as an array using the key combo of CTRL,SHIFT,ENTER:
>>
>> =MIN(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))
>>
>> =MAX(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))
>>
>> =AVERAGE(IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))
>>
>> If you wanted to find the MIN, MAX or AVG for energies 2 to 4 for
>> angles -3
>> to 2:
>>
>> A10 = 2
>> A11 = 4
>> A14 = -3
>> A15 = 2
>>
>> Formulas array entered:
>>
>> =MIN(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))
>>
>> =MAX(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))
>>
>> =AVERAGE(IF(A2:A7>=A14,IF(A2:A7<=A15,IF(B1:F1>=A10,IF(B1:F1<=A11,Tbl)))))
>>
>> If you want the corresponding angle for the MIN or MAX of any SINGLE
>> energy:
>> For example, energy 3:
>>
>> A10 = 3
>>
>> You would probably want to use a different cell to hold this variable
>> but I
>> just used A10 as the example.
>>
>> Normally entered:
>>
>> =INDEX(A2:A7,MATCH(MIN(INDEX(Tbl,,A10)),INDEX(Tbl,,A10),0))
>>
>> =INDEX(A2:A7,MATCH(MAX(INDEX(Tbl,,A10)),INDEX(Tbl,,A10),0))
>>
>> If you'd like a sample file to study this I'll be glad to put
>> something
>> together. Just let me know how to contact you.
>>
>> Biff
>>
>> "Ron H" <[email protected]> wrote in
>> message news:[email protected]...
>> >
>> > If it can be done without Pivot Tables I would gladly accept help
>> with
>> > formulas.
>> > I just thought that Pivot Tables would be the quickest way to check
>> the
>> > values within a range, by using the grouping function.
>> >
>> >
>> > --
>> > 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
>> >
>
>
> --
> 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
>