Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert PivotTable data into a worksheet list | Excel Worksheet Functions | |||
Macro to Synchronize data frm svrl workbooks & columns to 1 workbo | Excel Discussion (Misc queries) | |||
How do i copy columns of data in notepad into microsoft excel? | New Users to Excel | |||
Rearrange data columns in Pivot Table | Excel Discussion (Misc queries) | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |