|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 4th, 2007, 08:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condensing data from 3D to 2D
Hi there,
I have a spreadsheet containing data representing a 3 dimensional data structure in the following form; Z1 Z2 Z3 Z4 ... Y1 X1 X1 X1 X1 Y1 X2 X2 X2 X2 Y1 X3 X3 X3 X3 Y1 X4 X4 X4 X4 Y2 X1 X1 X1 X1 Y2 X2 X2 X2 X2 Y2 X3 X3 X3 X3 Y2 X4 X4 X4 X4 Y3 X1 X1 X1 X1 Y3 X2 X2 X2 X2 Y3 X3 X3 X3 X3 Y3 X4 X4 X4 X4 .... .... and so on In other words for every value Y (of which there are 8800 in total) there are four X values and I have one set of data (8800 x 4) per month (with nine years of data in total), so 3,801,600 data items in total (8800 x 4 x 12 x 9) What I would like to do is average the four X values for each value of Y and re-display it on a new sheet something like... Z1 Z2 Z3 Z4 ... Y1 Xm Xm Xm Xm ... Y2 Xm Xm Xm Xm ... Y3 Xm Xm Xm Xm ... Y4 Xm Xm Xm Xm ... ..... ... ... and so on However, when I construct the first line of the new file and then try to drag it I don't get the right results i.e. if the first value of Xm is given by AVERAGE(B1:B4) and the second is given AVERAGE(B5:B8), I actually get AVERAGE(B2:B5). Even when I construct the first two lines of the file and drag it I still don't get the right answer. How can I achieve this simple action without having to resort to VBA? I'm sure I'm missing something really obvious, so if I'm being a total noob please tell me :-) Thanks in advance Ian 
|
|
#2
December 4th, 2007, 08:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing data from 3D to 2D
"Garbin" wrote in message
... Hi there, I have a spreadsheet containing data representing a 3 dimensional data structure in the following form; Z1 Z2 Z3 Z4 ... Y1 X1 X1 X1 X1 Y1 X2 X2 X2 X2 Y1 X3 X3 X3 X3 Y1 X4 X4 X4 X4 Y2 X1 X1 X1 X1 Y2 X2 X2 X2 X2 Y2 X3 X3 X3 X3 Y2 X4 X4 X4 X4 Y3 X1 X1 X1 X1 Y3 X2 X2 X2 X2 Y3 X3 X3 X3 X3 Y3 X4 X4 X4 X4 ... .... and so on In other words for every value Y (of which there are 8800 in total) there are four X values and I have one set of data (8800 x 4) per month (with nine years of data in total), so 3,801,600 data items in total (8800 x 4 x 12 x 9) What I would like to do is average the four X values for each value of Y and re-display it on a new sheet something like... Z1 Z2 Z3 Z4 ... Y1 Xm Xm Xm Xm ... Y2 Xm Xm Xm Xm ... Y3 Xm Xm Xm Xm ... Y4 Xm Xm Xm Xm ... .... ... ... and so on However, when I construct the first line of the new file and then try to Sorry I mean 'new sheet' not 'new file' drag it I don't get the right results i.e. if the first value of Xm is given by AVERAGE(B1:B4) and the second is given AVERAGE(B5:B8), I actually get AVERAGE(B2:B5). Even when I construct the first two lines of the file and drag it I still don't get the right answer. How can I achieve this simple action without having to resort to VBA? I'm sure I'm missing something really obvious, so if I'm being a total noob please tell me :-) Thanks in advance Ian
|
|
#3
December 4th, 2007, 08:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing data from 3D to 2D
Post the failing formula.
Alan Beban Garbin wrote: "Garbin" wrote in message ... Hi there, I have a spreadsheet containing data representing a 3 dimensional data structure in the following form; Z1 Z2 Z3 Z4 ... Y1 X1 X1 X1 X1 Y1 X2 X2 X2 X2 Y1 X3 X3 X3 X3 Y1 X4 X4 X4 X4 Y2 X1 X1 X1 X1 Y2 X2 X2 X2 X2 Y2 X3 X3 X3 X3 Y2 X4 X4 X4 X4 Y3 X1 X1 X1 X1 Y3 X2 X2 X2 X2 Y3 X3 X3 X3 X3 Y3 X4 X4 X4 X4 ... .... and so on In other words for every value Y (of which there are 8800 in total) there are four X values and I have one set of data (8800 x 4) per month (with nine years of data in total), so 3,801,600 data items in total (8800 x 4 x 12 x 9) What I would like to do is average the four X values for each value of Y and re-display it on a new sheet something like... Z1 Z2 Z3 Z4 ... Y1 Xm Xm Xm Xm ... Y2 Xm Xm Xm Xm ... Y3 Xm Xm Xm Xm ... Y4 Xm Xm Xm Xm ... .... ... ... and so on However, when I construct the first line of the new file and then try to Sorry I mean 'new sheet' not 'new file' drag it I don't get the right results i.e. if the first value of Xm is given by AVERAGE(B1:B4) and the second is given AVERAGE(B5:B8), I actually get AVERAGE(B2:B5). Even when I construct the first two lines of the file and drag it I still don't get the right answer. How can I achieve this simple action without having to resort to VBA? I'm sure I'm missing something really obvious, so if I'm being a total noob please tell me :-) Thanks in advance Ian
|
|
#4
December 4th, 2007, 09:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing data from 3D to 2D
Ok,
Original data is in sheet1, condensed data on sheet2... Cell A1 on sheet2 contains a reference number carried forward from sheet1 Cell B1 on sheet2 contains the formula =AVERAGE(Sheet1!C2:C5) Cell C1 on sheet2 contains the formula =AVERAGE(Sheet1!D2  5)Cell D1 on sheet2 contains the formula =AVERAGE(Sheet1!E2:E5) and so on... Cell B2 on sheet2 should have the formula =AVERAGE(Sheet1!C6:C9) Cell C2 on sheet2 should have the formula =AVERAGE(Sheet1!D6 9)Cell D2 on sheet2 should have the formula =AVERAGE(Sheet1!E6:E9) but actually reads (when cells in row 1 dragged to fill the cells below)... Cell B2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!C3:C6) Cell C2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!D3 6)Cell D2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!E3:E6) Ian "Alan Beban" wrote in message ... Post the failing formula. Alan Beban Garbin wrote: "Garbin" wrote in message ... Hi there, I have a spreadsheet containing data representing a 3 dimensional data structure in the following form; Z1 Z2 Z3 Z4 ... Y1 X1 X1 X1 X1 Y1 X2 X2 X2 X2 Y1 X3 X3 X3 X3 Y1 X4 X4 X4 X4 Y2 X1 X1 X1 X1 Y2 X2 X2 X2 X2 Y2 X3 X3 X3 X3 Y2 X4 X4 X4 X4 Y3 X1 X1 X1 X1 Y3 X2 X2 X2 X2 Y3 X3 X3 X3 X3 Y3 X4 X4 X4 X4 ... .... and so on In other words for every value Y (of which there are 8800 in total) there are four X values and I have one set of data (8800 x 4) per month (with nine years of data in total), so 3,801,600 data items in total (8800 x 4 x 12 x 9) What I would like to do is average the four X values for each value of Y and re-display it on a new sheet something like... Z1 Z2 Z3 Z4 ... Y1 Xm Xm Xm Xm ... Y2 Xm Xm Xm Xm ... Y3 Xm Xm Xm Xm ... Y4 Xm Xm Xm Xm ... .... ... ... and so on However, when I construct the first line of the new file and then try to Sorry I mean 'new sheet' not 'new file' drag it I don't get the right results i.e. if the first value of Xm is given by AVERAGE(B1:B4) and the second is given AVERAGE(B5:B8), I actually get AVERAGE(B2:B5). Even when I construct the first two lines of the file and drag it I still don't get the right answer. How can I achieve this simple action without having to resort to VBA? I'm sure I'm missing something really obvious, so if I'm being a total noob please tell me :-) Thanks in advance Ian
|
|
#5
December 4th, 2007, 11:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing data from 3D to 2D
Garbin wrote:
Ok, Original data is in sheet1, condensed data on sheet2... Cell A1 on sheet2 contains a reference number carried forward from sheet1 Cell B1 on sheet2 contains the formula =AVERAGE(Sheet1!C2:C5) Cell C1 on sheet2 contains the formula =AVERAGE(Sheet1!D2 5)Cell D1 on sheet2 contains the formula =AVERAGE(Sheet1!E2:E5) and so on... Cell B2 on sheet2 should have the formula =AVERAGE(Sheet1!C6:C9) Cell C2 on sheet2 should have the formula =AVERAGE(Sheet1!D6 9)Cell D2 on sheet2 should have the formula =AVERAGE(Sheet1!E6:E9) but actually reads (when cells in row 1 dragged to fill the cells below)... Cell B2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!C3:C6) Cell C2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!D3 6)Cell D2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!E3:E6) Ian In B1 on sheet2 =AVERAGE(INDIRECT("Sheet1!C"&ROW(H$2)+4*(ROW(H1)-1)&":C"&ROW(H$2)+4*(ROW(H1)-1)+3)) In C1 =AVERAGE(INDIRECT("Sheet1!D"&ROW(H$2)+4*(ROW(H1)-1)&" "&ROW(H$2)+4*(ROW(H1)-1)+3))etc., copied down Alan Beban
|
|
#6
December 5th, 2007, 12:06 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing data from 3D to 2D
This is an ideal application for Pivot Table.
No formula or code is required. Assume your data looks like this: Z1 Z2 Z3 Z4 Y1 27 81 20 55 Y1 39 21 93 23 Y1 75 63 86 37 Y1 82 73 61 22 Y2 43 92 86 24 Y2 67 36 67 64 Y2 44 22 32 96 Y2 31 40 38 91 Y3 91 60 37 17 Y3 19 86 31 37 Y3 35 72 84 88 Y3 76 93 58 27 Data Pivot Table Multiple Consolidation Ranges Range: Select the above 13 x 5 array Layout: DATA = Average of Value Options: Uncheck Grand Totals, AutoFormat The result will look like this: Average of Value Column Row Z1 Z2 Z3 Z4 Y1 55.75 59.50 65.00 34.25 Y2 46.25 47.50 55.75 68.75 Y3 55.25 77.75 52.50 42.25 Month and Year data can be added in the Range table and shown in PAGE fields. More at Debra Dalgleish site: www.contextures.com
|
|
#7
December 5th, 2007, 06:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing data from 3D to 2D
Thankyou for the solution - it worked a treat.
"Herbert Seidenberg" wrote in message ... This is an ideal application for Pivot Table. No formula or code is required. Assume your data looks like this: Z1 Z2 Z3 Z4 Y1 27 81 20 55 Y1 39 21 93 23 Y1 75 63 86 37 Y1 82 73 61 22 Y2 43 92 86 24 Y2 67 36 67 64 Y2 44 22 32 96 Y2 31 40 38 91 Y3 91 60 37 17 Y3 19 86 31 37 Y3 35 72 84 88 Y3 76 93 58 27 Data Pivot Table Multiple Consolidation Ranges Range: Select the above 13 x 5 array Layout: DATA = Average of Value Options: Uncheck Grand Totals, AutoFormat The result will look like this: Average of Value Column Row Z1 Z2 Z3 Z4 Y1 55.75 59.50 65.00 34.25 Y2 46.25 47.50 55.75 68.75 Y3 55.25 77.75 52.50 42.25 Month and Year data can be added in the Range table and shown in PAGE fields. More at Debra Dalgleish site: www.contextures.com
|
|
#8
December 5th, 2007, 06:29 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Condensing data from 3D to 2D
Thankyou for the solution to my problem which you posted. In the end I went
with the pivot-table solution proposed for me elsewhere. However, I will be keeping your code in hand for the future. "Alan Beban" wrote in message ... Garbin wrote: Ok, Original data is in sheet1, condensed data on sheet2... Cell A1 on sheet2 contains a reference number carried forward from sheet1 Cell B1 on sheet2 contains the formula =AVERAGE(Sheet1!C2:C5) Cell C1 on sheet2 contains the formula =AVERAGE(Sheet1!D2 5)Cell D1 on sheet2 contains the formula =AVERAGE(Sheet1!E2:E5) and so on... Cell B2 on sheet2 should have the formula =AVERAGE(Sheet1!C6:C9) Cell C2 on sheet2 should have the formula =AVERAGE(Sheet1!D6 9)Cell D2 on sheet2 should have the formula =AVERAGE(Sheet1!E6:E9) but actually reads (when cells in row 1 dragged to fill the cells below)... Cell B2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!C3:C6) Cell C2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!D3 6)Cell D2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!E3:E6) Ian In B1 on sheet2 =AVERAGE(INDIRECT("Sheet1!C"&ROW(H$2)+4*(ROW(H1)-1)&":C"&ROW(H$2)+4*(ROW(H1)-1)+3)) In C1 =AVERAGE(INDIRECT("Sheet1!D"&ROW(H$2)+4*(ROW(H1)-1)&" "&ROW(H$2)+4*(ROW(H1)-1)+3))etc., copied down Alan Beban
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
