Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ron H
 
Posts: n/a
Default Data in table, may need to convert to columns with OFFSET?


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

  #2   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in
message ...

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



  #3   Report Post  
Ron H
 
Posts: n/a
Default


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

  #4   Report Post  
Biff
 
Posts: n/a
Default

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" wrote in
message ...

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



  #5   Report Post  
Ron H
 
Posts: n/a
Default


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" wrote in
message ...

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



  #6   Report Post  
Biff
 
Posts: n/a
Default

Glad to help. Thanks for the feedback!

Biff

"Ron H" wrote in
message ...

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" wrote in
message ...

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert PivotTable data into a worksheet list sansk_23 Excel Worksheet Functions 3 May 2nd 05 09:51 AM
Macro to Synchronize data frm svrl workbooks & columns to 1 workbo jbsand1001 Excel Discussion (Misc queries) 1 April 28th 05 10:42 AM
How do i copy columns of data in notepad into microsoft excel? JP New Users to Excel 2 February 10th 05 10:47 PM
Rearrange data columns in Pivot Table hedrew3 Excel Discussion (Misc queries) 5 February 5th 05 08:44 AM
Convert data type of cells to Text,Number,Date and Time Kevin Excel Worksheet Functions 1 December 31st 04 01:57 PM


All times are GMT +1. The time now is 07:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"