# Use of Row() in array formula not working

1. ## Use of Row() in array formula not working

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

2. ## Re: Use of Row() in array formula not working

Maybe like this.

First re-arange the data (with an macro).

After that I made an pivot table of it.

See the attached file.

3. ## Re: Use of Row() in array formula not working

Hello jsw,

ROW returns an "array" like {3} even when it'a a single value - this works perfectly well in some functions but others don't like it - you've found one of the latter. You can wrap it in another function like

SUM(ROW())

....but it's better (more robust) to use ROWS function instead, so in I3 instead of ROW()-3 use

ROWS(I\$3:I3)-1

4. ## Re: Use of Row() in array formula not working

Thanks DaddyLongLegs for the simple explanation and the solution. Your recommended solution works perfectly.
Thanks also to Oeldere - the actual data has a lot of dependencies so not easy to rearrange. I will have a good look through your spreadsheet though, as the pivot table layout might be worth the effort.
jsw

There are currently 1 users browsing this thread. (0 members and 1 guests)