Hello, I have data laid out like the simplified workbook attached - a group of channel values split into bins (0, 0.5, 1...) for each device for each year. On sheet 'Calc', I am calculating summary data, in this case adding up all the monthly Channel1 data points in a year for each bin for each device, for each year.
The following array formula works (used in column H):
=SUM((MOD(COLUMN(INDIRECT("'Data"&I$1&"'!C"&ROW($C$3)&":CT"&ROW($C$3)))-COLUMN(INDIRECT("'Data"&I$1&"'!C"&ROW($C$3))),3)=0)*OFFSET(INDIRECT("'Data"&I$1&"'!C3:CT3"),MATCH(OFFSET($B$2,ROUNDDOWN(($L3-3)/7,0)*7,0)&", "&I$1,INDIRECT("'Data"&I$1&"'!$B$2:$B$10000"),0)-1+2*$B3,0))
First part of the formula identifies every third column from the reference column on the appropriate data sheet, then multiply that array by the second part of the formula, which looks up the appropriate device row on the appropriate data sheet.
This formula refers to cell $L3 in the match function, which contains the formula =row(). If I put the Row() function into the formula in place of $L3 (as I've done in attached cell I3), I get #VALUE result.
I cant figure out why. Can anyone tell me why ROW() wont work in place of $L3, and how might I make the formula work such that I dont have to have a special column such as $L which contains the row numbers?
Thank you,
jsw
Bookmarks