Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |