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
|