|
| 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
February 4th, 2010, 10:50 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Statistical functions on non-consequtive columns
Hi,
I have several data tables, where every second column is an index and the other columns uses the index to fetch the corresponding data. If any data is invalid, #N/A is returned, so that the data can be shown in graphs (used "" instead of #N/A in the beginning, but this made the graphs very noisy, dropping to zero). For each column of data the MIN, MAX and AVG is given by an array formula, e.g. {=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}. So far so good. The problem is to give the average of each row in the tables, the RowAVG below, since the data are in non-consequtive columns. I would be greatful to receive a solution to this problem, without having to duplicate the Data columns into a similar table but without the Index columns. Index1 Data1 Index2 Data2 Index3 Data3 RowAVG 1 10 1 20 1 30 20 2 15 2 #N/A 2 35 25 3 35 3 20 3 65 40 ColAVG 20 20 43.3 ColMIN 10 20 30 
|
|
#2
February 4th, 2010, 11:30 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Statistical functions on non-consequtive columns
One way:
=SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),A2:G2)/SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),--(ISNUMBER(A2:G2))) Fill it down! -- Regards! Stefi „Frode” ezt *rta: Hi, I have several data tables, where every second column is an index and the other columns uses the index to fetch the corresponding data. If any data is invalid, #N/A is returned, so that the data can be shown in graphs (used "" instead of #N/A in the beginning, but this made the graphs very noisy, dropping to zero). For each column of data the MIN, MAX and AVG is given by an array formula, e.g. {=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}. So far so good. The problem is to give the average of each row in the tables, the RowAVG below, since the data are in non-consequtive columns. I would be greatful to receive a solution to this problem, without having to duplicate the Data columns into a similar table but without the Index columns. Index1 Data1 Index2 Data2 Index3 Data3 RowAVG 1 10 1 20 1 30 20 2 15 2 #N/A 2 35 25 3 35 3 20 3 65 40 ColAVG 20 20 43.3 ColMIN 10 20 30
|
|
#3
February 4th, 2010, 11:44 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Statistical functions on non-consequtive columns
Sorry, this is the correct version for AVG:
=SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),A2:F2)/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(ISNUMBER(A2:F2))) -- Regards! Stefi „Stefi” ezt *rta: One way: =SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),A2:G2)/SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),--(ISNUMBER(A2:G2))) Fill it down! -- Regards! Stefi „Frode” ezt *rta: Hi, I have several data tables, where every second column is an index and the other columns uses the index to fetch the corresponding data. If any data is invalid, #N/A is returned, so that the data can be shown in graphs (used "" instead of #N/A in the beginning, but this made the graphs very noisy, dropping to zero). For each column of data the MIN, MAX and AVG is given by an array formula, e.g. {=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}. So far so good. The problem is to give the average of each row in the tables, the RowAVG below, since the data are in non-consequtive columns. I would be greatful to receive a solution to this problem, without having to duplicate the Data columns into a similar table but without the Index columns. Index1 Data1 Index2 Data2 Index3 Data3 RowAVG 1 10 1 20 1 30 20 2 15 2 #N/A 2 35 25 3 35 3 20 3 65 40 ColAVG 20 20 43.3 ColMIN 10 20 30
|
|
#4
February 4th, 2010, 12:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Statistical functions on non-consequtive columns
Thanks Stefi, but this returned #N/A for the 2nd row where one of the data
fields was #N/A. It worked for the rows where all data fields were valid. -- Regards Frode "Stefi" wrote: Sorry, this is the correct version for AVG: =SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),A2:F2)/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(ISNUMBER(A2:F2))) -- Regards! Stefi „Stefi” ezt *rta: One way: =SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),A2:G2)/SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),--(ISNUMBER(A2:G2))) Fill it down! -- Regards! Stefi „Frode” ezt *rta: Hi, I have several data tables, where every second column is an index and the other columns uses the index to fetch the corresponding data. If any data is invalid, #N/A is returned, so that the data can be shown in graphs (used "" instead of #N/A in the beginning, but this made the graphs very noisy, dropping to zero). For each column of data the MIN, MAX and AVG is given by an array formula, e.g. {=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}. So far so good. The problem is to give the average of each row in the tables, the RowAVG below, since the data are in non-consequtive columns. I would be greatful to receive a solution to this problem, without having to duplicate the Data columns into a similar table but without the Index columns. Index1 Data1 Index2 Data2 Index3 Data3 RowAVG 1 10 1 20 1 30 20 2 15 2 #N/A 2 35 25 3 35 3 20 3 65 40 ColAVG 20 20 43.3 ColMIN 10 20 30
|
|
#5
February 4th, 2010, 02:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Statistical functions on non-consequtive columns
Hi again,
Got it working if bringing in the ISNUMBER check twice. I don't know if this is the best way, but it works. Thanks for the hint. {=SUMPRODUCT(--(MOD(COLUMN(FM3:FR3);2)=0);IF(ISNUMBER(FM3:FR3);FM 3:FR3;""))/SUMPRODUCT(--(MOD(COLUMN(FM3:FR3);2)=0);(IF(ISNUMBER(FM3:FR3);1 ;0)))} -- Regards Frode "Frode" wrote: Thanks Stefi, but this returned #N/A for the 2nd row where one of the data fields was #N/A. It worked for the rows where all data fields were valid. -- Regards Frode "Stefi" wrote: Sorry, this is the correct version for AVG: =SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),A2:F2)/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(ISNUMBER(A2:F2))) -- Regards! Stefi „Stefi” ezt *rta: One way: =SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),A2:G2)/SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),--(ISNUMBER(A2:G2))) Fill it down! -- Regards! Stefi „Frode” ezt *rta: Hi, I have several data tables, where every second column is an index and the other columns uses the index to fetch the corresponding data. If any data is invalid, #N/A is returned, so that the data can be shown in graphs (used "" instead of #N/A in the beginning, but this made the graphs very noisy, dropping to zero). For each column of data the MIN, MAX and AVG is given by an array formula, e.g. {=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}. So far so good. The problem is to give the average of each row in the tables, the RowAVG below, since the data are in non-consequtive columns. I would be greatful to receive a solution to this problem, without having to duplicate the Data columns into a similar table but without the Index columns. Index1 Data1 Index2 Data2 Index3 Data3 RowAVG 1 10 1 20 1 30 20 2 15 2 #N/A 2 35 25 3 35 3 20 3 65 40 ColAVG 20 20 43.3 ColMIN 10 20 30
|
|
#6
February 4th, 2010, 02:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Statistical functions on non-consequtive columns
Sorry, I forgot to mention that these are array formulae to be confirmed with
Ctrl+Shift+Enter. This is a bit reformatted formula: for Avg: =SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),IF(ISNA(A2:F2),0,A2:F2))/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(NOT(ISNA(A2:F2)))) for Max: =MAX(--(MOD(COLUMN(A2:F2),2)=0),IF(ISNA(A2:F2),0,A2:F2)) Still thinking on Min. -- Regards! Stefi „Frode” ezt *rta: Thanks Stefi, but this returned #N/A for the 2nd row where one of the data fields was #N/A. It worked for the rows where all data fields were valid. -- Regards Frode "Stefi" wrote: Sorry, this is the correct version for AVG: =SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),A2:F2)/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(ISNUMBER(A2:F2))) -- Regards! Stefi „Stefi” ezt *rta: One way: =SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),A2:G2)/SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),--(ISNUMBER(A2:G2))) Fill it down! -- Regards! Stefi „Frode” ezt *rta: Hi, I have several data tables, where every second column is an index and the other columns uses the index to fetch the corresponding data. If any data is invalid, #N/A is returned, so that the data can be shown in graphs (used "" instead of #N/A in the beginning, but this made the graphs very noisy, dropping to zero). For each column of data the MIN, MAX and AVG is given by an array formula, e.g. {=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}. So far so good. The problem is to give the average of each row in the tables, the RowAVG below, since the data are in non-consequtive columns. I would be greatful to receive a solution to this problem, without having to duplicate the Data columns into a similar table but without the Index columns. Index1 Data1 Index2 Data2 Index3 Data3 RowAVG 1 10 1 20 1 30 20 2 15 2 #N/A 2 35 25 3 35 3 20 3 65 40 ColAVG 20 20 43.3 ColMIN 10 20 30
|
|
#7
February 4th, 2010, 02:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Statistical functions on non-consequtive columns
For Min (also array formula):
=MIN(IF(MOD(COLUMN(A2:F2),2)=0,1,6.022*10^23)*IF(I SNA(A2:F2),6.022*10^23,A2:F2)) -- Regards! Stefi „Stefi” ezt *rta: Sorry, I forgot to mention that these are array formulae to be confirmed with Ctrl+Shift+Enter. This is a bit reformatted formula: for Avg: =SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),IF(ISNA(A2:F2),0,A2:F2))/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(NOT(ISNA(A2:F2)))) for Max: =MAX(--(MOD(COLUMN(A2:F2),2)=0),IF(ISNA(A2:F2),0,A2:F2)) Still thinking on Min. -- Regards! Stefi „Frode” ezt *rta: Thanks Stefi, but this returned #N/A for the 2nd row where one of the data fields was #N/A. It worked for the rows where all data fields were valid. -- Regards Frode "Stefi" wrote: Sorry, this is the correct version for AVG: =SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),A2:F2)/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(ISNUMBER(A2:F2))) -- Regards! Stefi „Stefi” ezt *rta: One way: =SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),A2:G2)/SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),--(ISNUMBER(A2:G2))) Fill it down! -- Regards! Stefi „Frode” ezt *rta: Hi, I have several data tables, where every second column is an index and the other columns uses the index to fetch the corresponding data. If any data is invalid, #N/A is returned, so that the data can be shown in graphs (used "" instead of #N/A in the beginning, but this made the graphs very noisy, dropping to zero). For each column of data the MIN, MAX and AVG is given by an array formula, e.g. {=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}. So far so good. The problem is to give the average of each row in the tables, the RowAVG below, since the data are in non-consequtive columns. I would be greatful to receive a solution to this problem, without having to duplicate the Data columns into a similar table but without the Index columns. Index1 Data1 Index2 Data2 Index3 Data3 RowAVG 1 10 1 20 1 30 20 2 15 2 #N/A 2 35 25 3 35 3 20 3 65 40 ColAVG 20 20 43.3 ColMIN 10 20 30
|
|
#8
February 4th, 2010, 03:26 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Statistical functions on non-consequtive columns
Thank you very much Stefi. I've now got all my functions. I didn't get it
right with your MAX function, but I just modified your MIN function like this: {=MAX(IF(MOD(COLUMN(FM3:FR3);2)=0;1;0)*IF(ISNA(FM3 :FR3);0;FM3:FR3))} -- Regards Frode "Stefi" wrote: For Min (also array formula): =MIN(IF(MOD(COLUMN(A2:F2),2)=0,1,6.022*10^23)*IF(I SNA(A2:F2),6.022*10^23,A2:F2)) -- Regards! Stefi „Stefi” ezt *rta: Sorry, I forgot to mention that these are array formulae to be confirmed with Ctrl+Shift+Enter. This is a bit reformatted formula: for Avg: =SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),IF(ISNA(A2:F2),0,A2:F2))/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(NOT(ISNA(A2:F2)))) for Max: =MAX(--(MOD(COLUMN(A2:F2),2)=0),IF(ISNA(A2:F2),0,A2:F2)) Still thinking on Min. -- Regards! Stefi „Frode” ezt *rta: Thanks Stefi, but this returned #N/A for the 2nd row where one of the data fields was #N/A. It worked for the rows where all data fields were valid. -- Regards Frode "Stefi" wrote: Sorry, this is the correct version for AVG: =SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),A2:F2)/SUMPRODUCT(--(MOD(COLUMN(A2:F2),2)=0),--(ISNUMBER(A2:F2))) -- Regards! Stefi „Stefi” ezt *rta: One way: =SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),A2:G2)/SUMPRODUCT(--(MOD(COLUMN(A2:G2),2)=0),--(ISNUMBER(A2:G2))) Fill it down! -- Regards! Stefi „Frode” ezt *rta: Hi, I have several data tables, where every second column is an index and the other columns uses the index to fetch the corresponding data. If any data is invalid, #N/A is returned, so that the data can be shown in graphs (used "" instead of #N/A in the beginning, but this made the graphs very noisy, dropping to zero). For each column of data the MIN, MAX and AVG is given by an array formula, e.g. {=AVERAGE(IF(ISNUMBER(B11:B754);B11:B754;""))}. So far so good. The problem is to give the average of each row in the tables, the RowAVG below, since the data are in non-consequtive columns. I would be greatful to receive a solution to this problem, without having to duplicate the Data columns into a similar table but without the Index columns. Index1 Data1 Index2 Data2 Index3 Data3 RowAVG 1 10 1 20 1 30 20 2 15 2 #N/A 2 35 25 3 35 3 20 3 65 40 ColAVG 20 20 43.3 ColMIN 10 20 30
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
