Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Help
For any Row:
Columns 1-24 contain whole numbers. Column 25 contains a whole number which is less than 8, representing the number of columns to consider when averaging values. Column 26 contains a formula (or resulting value) indicating the beginning column, which has the highest average value, for those columns averaged, based on the value in column 25. So if Column 25=5, then Column 26 would average columns 1-5, then 2-6, then 3-7, 4-8, etc. to determine which of these averages is highest, and indicate the beginning Column Number of that average in Column 26. I hope I have explained this well enough for someone to help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Help
On Wed, 22 Jul 2009 17:03:59 -0700 (PDT), iamnu
wrote: For any Row: Columns 1-24 contain whole numbers. Column 25 contains a whole number which is less than 8, representing the number of columns to consider when averaging values. Column 26 contains a formula (or resulting value) indicating the beginning column, which has the highest average value, for those columns averaged, based on the value in column 25. So if Column 25=5, then Column 26 would average columns 1-5, then 2-6, then 3-7, 4-8, etc. to determine which of these averages is highest, and indicate the beginning Column Number of that average in Column 26. I hope I have explained this well enough for someone to help. With your numbers to be averages in cells A1:X1, the number of cells to average in cell Y1, try the following formula in cell Z1: =MATCH(MAX(MMULT(A1:X1,TRANSPOSE(((COLUMN(OFFSET(A 1:A1,,,,24)))< (ROW(A3:A26)))*((COLUMN(OFFSET(A1:A1,,,,24)))(ROW (A2:A25)-Y1))))), MMULT(A1:X1,TRANSPOSE(((COLUMN(OFFSET(A1:A1,,,,24) ))< (ROW(A3:A26)))*((COLUMN(OFFSET(A1:A1,,,,24)))(ROW (A2:A25)-Y1)))),0)+2-Y1 Note: This is an array formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Help
On Thu, 23 Jul 2009 07:18:04 GMT, Lars-Åke Aspelin
wrote: On Wed, 22 Jul 2009 17:03:59 -0700 (PDT), iamnu wrote: For any Row: Columns 1-24 contain whole numbers. Column 25 contains a whole number which is less than 8, representing the number of columns to consider when averaging values. Column 26 contains a formula (or resulting value) indicating the beginning column, which has the highest average value, for those columns averaged, based on the value in column 25. So if Column 25=5, then Column 26 would average columns 1-5, then 2-6, then 3-7, 4-8, etc. to determine which of these averages is highest, and indicate the beginning Column Number of that average in Column 26. I hope I have explained this well enough for someone to help. With your numbers to be averages in cells A1:X1, the number of cells to average in cell Y1, try the following formula in cell Z1: =MATCH(MAX(MMULT(A1:X1,TRANSPOSE(((COLUMN(OFFSET( A1:A1,,,,24)))< (ROW(A3:A26)))*((COLUMN(OFFSET(A1:A1,,,,24)))(RO W(A2:A25)-Y1))))), MMULT(A1:X1,TRANSPOSE(((COLUMN(OFFSET(A1:A1,,,,24 )))< (ROW(A3:A26)))*((COLUMN(OFFSET(A1:A1,,,,24)))(RO W(A2:A25)-Y1)))),0)+2-Y1 Note: This is an array formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke The previously posted formula does not handle negative numbers correctly. I hope this one will do that: =MATCH(MAX(IF(COLUMN(A1:X1)=Y1,MMULT(A1:X1,TRANSP OSE( ((COLUMN(A1:X1))<(ROW(A2:A25)))*((COLUMN(A1:X1))( ROW(A1:A24)-Y1)))))), IF(COLUMN(A1:X1)=Y1,MMULT(A1:X1,TRANSPOSE(((COLUM N(A1:X1))< (ROW(A2:A25)))*((COLUMN(A1:X1))(ROW(A1:A24)-Y1))))),0)+1-Y1 Note: This is also an anrray formula that should be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |