Help for Array function
 

Hello,

I need to calculate the squared differences among cells of multiple
separate sheets:

0 1 0 1 0
1 0 0 1 0
0 0 0 0 0
1 1 0 0 0
0 0 0 0 0
(sheet named "1")

0 1 0 0 0
1 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
(sheet named "2")

etc., etc. in total 15 sheets.

The "Calc" sheet tries to capture the squared difference of the
corresponding cells among any two of these sheets:

row 1 2 3 4 5 <---column indicator
1 0 48 103 47 81
2 48 0 117 63 81
3 103 117 0 108 134
4 47 63 108 0 86
5 81 81 134 86 0

I have succeeded using array formula:
=SUMSQ{'1'!$B$3:$AH$35-'2'!$B$3:$AH$35}/2 for any cell in the "Calc"
sheet. Yet when I tried to automate the process a bit by using the
column indicator & row indicator, it gives "#REF" error.

Does anyone have any idea?

Thanks a bunch!!!

Henry

Help for Array function
 

How are you using the column indicator in the worksheet.

It is probably something to do with referencings and you may need to use
indirect.
--
Hope this helps
Martin Fishlock


" wrote:

Hello,

I need to calculate the squared differences among cells of multiple
separate sheets:

0 1 0 1 0
1 0 0 1 0
0 0 0 0 0
1 1 0 0 0
0 0 0 0 0
(sheet named "1")

0 1 0 0 0
1 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
(sheet named "2")

etc., etc. in total 15 sheets.

The "Calc" sheet tries to capture the squared difference of the
corresponding cells among any two of these sheets:

row 1 2 3 4 5 <---column indicator
1 0 48 103 47 81
2 48 0 117 63 81
3 103 117 0 108 134
4 47 63 108 0 86
5 81 81 134 86 0

I have succeeded using array formula:
=SUMSQ{'1'!$B$3:$AH$35-'2'!$B$3:$AH$35}/2 for any cell in the "Calc"
sheet. Yet when I tried to automate the process a bit by using the
column indicator & row indicator, it gives "#REF" error.

Does anyone have any idea?

Thanks a bunch!!!

Henry