Define an array based on calculations

Hi,

well, I just miserably failed in posting my first thread. Hope I will
be luckier this time.

I have a couple of matrices (170) in one excel sheet and would like to
calculate the correlation coefficient of all pair combinations. That
will give me about 14000 correlation calculations.
I already specified the pair combinations, where each matrix got the
label 1, 2, 3... 170 and arranged in two different columns (AB, AC)
like this: 
1 2
1 3
..
65 102
..
169 170
All matrices are in the area of A1 to T4076, where each matrix has
20x20 cells with 4 empty rows between eachother.

Basically, I need to manage such a forumula:
'=CORREL($A($AB1*24):$T($AB1*24+20);$A($AC1*24):$T($AC1*24+20))'
Unfortunately, $A($AB1*24) is not a proper array definition in excel.

So, how do I manage to define a line that considers the current pair in
the columns AB and AC and based on the values in AB and AC retrieves the
areas for the desired 20x20 matrix to calculate the correlation
coefficient? 
I hope I was able to make myself clear and hope this wasn't asked
before in this forum, since I didn't know how to search for this
matter.

Thanks a lot!

Sam

PS.: I am using the most recent MS Excel version for macs.


-- 
Zammy-Sam
------------------------------------------------------------------------
Zammy-Sam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33036
View this thread: http://www.excelforum.com/showthread.php?threadid=528561

0
3/31/2006 2:55:44 PM
excel 39879 articles. 2 followers. Follow

3 Replies
386 Views

Similar Articles

[PageSpeed] 1

Hi Sam,

Look in HELP for the INDIRECT() function.

-- 
Kind regards,

Niek Otten

"Zammy-Sam" <Zammy-Sam.25jhdz_1143817203.5894@excelforum-nospam.com> wrote in message 
news:Zammy-Sam.25jhdz_1143817203.5894@excelforum-nospam.com...
>
> Hi,
>
> well, I just miserably failed in posting my first thread. Hope I will
> be luckier this time.
>
> I have a couple of matrices (170) in one excel sheet and would like to
> calculate the correlation coefficient of all pair combinations. That
> will give me about 14000 correlation calculations.
> I already specified the pair combinations, where each matrix got the
> label 1, 2, 3... 170 and arranged in two different columns (AB, AC)
> like this:
> 1 2
> 1 3
> .
> 65 102
> .
> 169 170
> All matrices are in the area of A1 to T4076, where each matrix has
> 20x20 cells with 4 empty rows between eachother.
>
> Basically, I need to manage such a forumula:
> '=CORREL($A($AB1*24):$T($AB1*24+20);$A($AC1*24):$T($AC1*24+20))'
> Unfortunately, $A($AB1*24) is not a proper array definition in excel.
>
> So, how do I manage to define a line that considers the current pair in
> the columns AB and AC and based on the values in AB and AC retrieves the
> areas for the desired 20x20 matrix to calculate the correlation
> coefficient?
> I hope I was able to make myself clear and hope this wasn't asked
> before in this forum, since I didn't know how to search for this
> matter.
>
> Thanks a lot!
>
> Sam
>
> PS.: I am using the most recent MS Excel version for macs.
>
>
> -- 
> Zammy-Sam
> ------------------------------------------------------------------------
> Zammy-Sam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33036
> View this thread: http://www.excelforum.com/showthread.php?threadid=528561
> 


0
nicolaus (2022)
3/31/2006 4:41:27 PM
Assuming your dat looks like this:
seta	setb	corr
1	2	-0.13
1	3	0.17
....	...
65	102	-0.02
....	...
169	170	0.33
with the columns named seta and setb and
the arrays named arr1 thru arr170,
then the formula under corr, in R1C1 style, is
=CORREL(INDIRECT("arr"&seta R),INDIRECT("arr"&setb R))
If naming the arrays is too much work, try this formula instead
=CORREL(INDEX(arra,(seta-1)*(arr_s+arr_d)+1,1):
           INDEX(arra,(seta-1)*(arr_s+arr_d)+arr_s+1,arr_d),
           INDEX(arra,(setb-1)*(arr_s+arr_d)+1,1):
           INDEX(arra,(setb-1)*(arr_s+arr_d)+arr_d,arr_d))
where arra is R1C1:R4076C20 (A1:T4076),
arr_d is 20 and arr_s is 4

0
3/31/2006 6:01:05 PM
Dear Niek Otten and Dear Herbert Seidenberg,

your input helped me out. Thank you very much!
The final formula is:
'=CORREL(INDIRECT("A"&(24*$AB1-23)):INDIRECT("T"&(24*$AB1-4));INDIRECT("A"&(24*$AC1-23)):INDIRECT("T"&(24*$AC1-4)))'

Cheers

Sa

--
Zammy-Sa
-----------------------------------------------------------------------
Zammy-Sam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3303
View this thread: http://www.excelforum.com/showthread.php?threadid=52856

0
4/3/2006 10:36:34 AM
Reply:

Similar Artilces:

Collections Management
Dynamics GP v9 - Collections Management Users are unable to use "user defined letters" (Word Documents) for mass mailings. They are limited to only using predefined letters, or printing each letter individually for every customer. Error received when users attempt to use a custom letter: -------------------------------------------------- Unhandled script exception: Cannot find report "COL_Reminder_UpcomingDue". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORT -------------------------------------------------- ---------------- This post is a suggestion for Microso...

Initializing a 2D array with image size
i have a structure 'struct Node' ; and inside the ....::OnOpenDocument(LPCTSTR lpszPathName) function is 'm_imName = lpszPathName;' . i m initializing my image with 'm_image = new MyImage();' then i m reading an image : 'm_image->ReadMyImage( (char*) LPCTSTR(m_imName)) ;' and i want to initialize my 2d array with image size that i ve just read : Node** m_dist = (Node**) new Node[m_image->m_height][m_image->m_width]; but i got error, it says "non-constant expression as array bound" , what can i do to initialize my array with the just read...

Adding variables to a static text control (displaying contents of array)
Hello, I was wondering if the following was possible ? I have a static text control in a dialog application. I also have an array which contains about 5 items. I would like the text control to display the contents of this array and then have a button which would move to next position into the array and thus the static text control would display something different ? How could achieve this? I'm using Visual Studio 6.0 and I'm quite new to MFC Many Thanks. >I have a static text control in a dialog application. I also have an >array which contains about 5 items. I would lik...

E-mail Excel worksheet based upon selection of combo box
I'm using the form toolbar combo-box with a pre-defined list that is on a hidden sheet. The list starts at 1, not 0. The goal that I am trying to reach is if a particular plant is selected in the combo-box (plant 1, plant 2, plant 3), when the e-mail button is clicked, it will send it to the person under that plant. I tried several different options including: Try 1: If ActiveSheet.Shapes("dd_plant").ListIndex = 2 Then .SendMail "asdf@gmail.com", "DP Routing Create/ Update/Change Form - Monroe", yes ElseIf ActiveSheet.Shapes("dd_plant"...

Email Address Based On Recipient Policy
Ok. I have this question. I recently upgraded my Exchange 5.5 to 2003 by adding 2003 Exchange to the organization. All mailboxes have been moved over. Now, my 5.5 was setup that email addresses get generated based on First Name and First Initial of Last name. The recipient policy on 2003 is still set to default that email address = user name that you create which is ok by me for now. Here comes the problem. Some users even before migration, we changed their email addresses to something else for all different reasons. So now their email addresses can be just first name or just l...

vlookup
I have an excel XP workbook with multiple worksheets where I need t lookup values in one spreadsheet and put the values in a cell i another worksheet. I have many columns that I need to lookup within my lookup range an certain of the columns are entered in alpha order. However, if I wan to insert new columns all of the existing column references in m lookup formula are now incorrect if they appear to the right of th inserted columns. Is there a way of refering to the coulmns relativel (e.g. 4 cols the the right of col A, which then get renumbered as ne columns are inserted to the left of them...

Application-defined or object-defined error
Hi, I find myself stumped by an incredibly easy piece of code and one that I have used before. I am getting the error: Run-time error '1004': Application-defined or object-defined error I am getting the error when I run the following code: Private Sub Workbook_Open() With Application .ScreenUpdating = False .DisplayAlerts = False Workbooks.Open "\\depot02\rel\www\internal\business_areas\edg\Metrics\Phones\HighHoldTimesDetailed.xls" Workbooks("HighHoldTimesDetailed.xls").Worksheets("data").Cells.Copy _ Workbooks("phoneholdtime...

Using Multidimensional Array
I am using multidimensional array in my function. dim aReturnValue() iIndexValue = 0 If iOccurrence > 0 Then bStringFound = True ' Prepare the return value ReDim Preserve aReturnValue(iIndexValue, 3) aReturnValue(iIndexValue,0) = sMatchedValue aReturnValue(iIndexValue,1) = iLineCount+1 aReturnValue(iIndexValue,2) = iOccurrence aReturnValue(iIndexValue,3) = sListOfMatchFoundItems iIndexValue = iIndexValue + 1 End If Problem is I am getting subscript out of range in ReDim Preserve aReturnValue(iIndexValue, 3) I checked it on the net... I came to know that When usi...

How to declare DefaultValue(type, string) for an Empty Array[0]
[System.Xml.Serialization.XmlArray("MenuItems")] [System.Xml.Serialization.XmlArrayItem("MenuItem", typeof(MenuItem))] [DefaultValue(typeof(MenuItem[]), "new MenuItem[0]")] public MenuItem[] MenuItems = new MenuItem[0]; I am trying to get rid of all the <MenuItem /> tags when serializing Thanks... ...

Variant with BSTR Array
Hi, I need some examples in VC++ as how to properly construct a variant with a BSTR array with variable length (VT_Array | VT_BSTR). Could anyone please help? Thanks. Best regard, Chaw-Chi Yu Use CComBSTR instead. That does all the initialization/cleanup for you. Usage here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/html /vcconProgrammingWithCComBSTR.asp -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Chaw-Chi Yu" <chawchiyu@hotmail.com> wrote in message news:OYUijSzxGHA.4336@TK2MSFTNGP06.phx.gbl... > Hi, > > I need some examples ...

Conditional Format Formula to color row based on text
I have reports showing internet usage for various employees. I have been asked to print these out and highlight the places the employees shouldn't be during working hours. I have searched looking for a example macro that might do this with out results. I have found that most people recommend Conditional Formatting to do this. I have looked for formula examples to change the color of a row. I can't find a example that will look for text. Could someone give me a example of a formula that will change the background color of a row depending on text in a cell on the row. ...

runtime error 2465: application-defined or object-defined error
I'm trying to use a button on a main form to change the sort order of a sub- subform.When I execute the following, I get runtime error 2465: application- defined or object-defined error. opting to debug, I find the line with orderbyon highlighted. Anybody see what's wrong? Private sub timesort_click() Forms![control]![worklist]![approved].Form.OrderBy = "Forms![control]! [worklist]![approved]![time]" Forms![control]![worklist]![approved].Form.OrderBy0n = True End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/a...

date calculation #6
hi i have a database where column b is date column c is customer name when i input customer name in the system it should check if the same customer name appears within 15 days of time from his last transaction. -- Nisha P so, if I understand correctly, you enter the date in column B, thencustoemr name in C. Once you do this, you want to be told if the customer has been added within 15 days of the date you just entered? If so: =if(C200="","",if(countif(B$2:B199,">=C200-15")>0,"Added within 15 days","Not Added")) Should work ...

creating an array
I want to create a time array. For example, cell A1 is the starting time of 6:00 AM, and cell B1 which contains the ending time of 2 PM. From 6 am to 2 pm is exactly 8 hours. I want to create an array of 8 elements with the starting value of 6:00 AM, and the last value in the array with the value of 2 pm. Of course, the array will start from cell C1 to J1 or 8 elements cell. Is there a formula to do? or is it possible to do this in excel at all? It would be similar to the one below. A B C D E F G --->&g...

how to do self-defined regression in excel?
Hi, I want to do regression ananlysis in excel using a sigmoid function, which is not offered in the general 6 functions in excel(by adding trendline). How can I do it? Thanks. The Analysis ToolPak - VBA addin has a regression function and probably everything else you want. Tools > AddIns then checkmark Analysis ToolPak - VBA Go back to Tools dropdown and it should be listed near the bottotm of the dropdown. >-----Original Message----- >Hi, I want to do regression ananlysis in excel using a >sigmoid function, which is not offered in the general 6 >functions in excel(by a...

Calculating missing numbers
I have a list of numbers from 1 to 1000 in Column A. Some of the numbers are missing; for example they may run 1, 2 3, 7 etc. I need a formula that will calculate the missing numbers and place them in Column B. Many thanks in advance. SiH, In cell B1, enter the formula =IF(ISERROR(MATCH(ROW(),A:A,FALSE)),ROW(),"") and copy down to B2:B1000. Copy all of B, paste special values, and then sort B ascending, and you will have your list. HTH, Bernie MS Excel MVP "SiH23" <SiH23@discussions.microsoft.com> wrote in message news:35797493-1BC3...

Report based on a filtered form
I need one of my report to show exactly the same records as one of my subform. I created a button doing this (which is working) : DoCmd.OpenReport "00-GetOutstandingSalesOrder", acViewPreview, , Form_CustomerOutstandingSalesSubForm.Filter The problem is if I close my report and remove all filter on the subform then click the button again, the last filter is still apply on my report. Would like to see all record if there is no filter apply. My Subform is in datasheet view which I filter using the filters toolbar button. I also have a report based on the exact same r...

Defining a variable to = MATCH
Dim thingy As Integer thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)" ActiveCell.Formula = _ "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))" Range("D26").Select my thingy doesnt seem to work..... no comment Any suggestions on correcting this? Mik -- Message posted from http://www.ExcelForum.com Try with thingy = Application.Match("DiceC", Sheets("qperiodagentperformance"...

Calculating age at different points in time
Using 2007, I need to calculate exact ages based on both full and partial dates that could range from the 1400's to current day. This is for a genealogy project. I would like to be able to enter a birth date into a cell which could be month-day-year if known or month-year if known or just year. In an adjacent column, say column 2, there would be a list of dates which would mark various points in time, like census dates, marriage, etc. These dates could also be month-day-year if known or month-year if known or just year. In column 3, 4, and 5, adjacent to column 2, I ne...

User-Defined type not defined?
I recently imported all my data into a blank DB now I am getting this error on: Dim wrk As Workspace Is that something to do with me importing into a new DB Thanks for any help...........Bob Sub SelAllNone(Optional SelectAll As Boolean = True) On Error GoTo stoprun Dim sqlStr As String Dim wrk As Workspace Dim db As Database Set wrk = DBEngine.Workspaces(0) Set db = CurrentDb sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";" wrk.BeginTrans db.Execute sqlStr, dbFailOnError wrk.CommitTrans Exit_Here: Set wrk = Nothing Set db = Nothing ...

"too many fields defined" error
I am trying to add a field to my table and keep getting an error message "too many fields defined" and it will not let me add the new field. Does anyone know what I have done to get this error and how I can fix it so I can add fields to my database? How many fields do you have in your table? The maximum number for a single table is 255. Note: it's rare that you would need more than 30 fields in a table if you've properly normalized your table structure. -- Ken Snell <MS ACCESS MVP> "Rachel" <Rachel@discussions.microsoft.com> wrote in ...

Excel 2007
How do I calculate what percentage of a year has elapsed? I have the date 31/12/09 in cell A1 and the date 31/05/09 in cell B1 and then I do this: =B1/A1*100 but this formula is wrong.How should I do the calculation? Thanks, John =1+(B1-A1)/365 If you want to account for leap years, use a third cell with 31-12-2008. If this is in A2, use this formula: =1+(B1-A1)/(A1-A2) -- Kind regards, Niek Otten Microsoft MVP - Excel "john" <john@discussions.microsoft.com> wrote in message news:6F1E94F8-3E3D-4361-BB77-A8E0231FC52F@microsoft.com... > How do I calculate what per...

show calculation
How can I show the multiple values entered in a cell for a simple sum ? At the moment, I enter all of it again into a comment, then print and show the comment (the recipient wants it all on paper). Is there a possibility to automatically show/print all the entries of a cell on the paper ? Help much appreciated ! Thanks, Anke Anke, Maybe Tools | Options | View | Window Options....Formulas Is that what you are looking for? Regards, Kevin "Anke" <anonymous@discussions.microsoft.com> wrote in message news:1166101c3bf7f$29d17310$a601280a@phx.gbl... > How can I show the ...

Arrays Containing Data from Multiple Worksheets
Maybe I'm just experiencing a brain fart, but I can't remember how to input arrays from multiple worksheets into a formula. Here's what I want to do... WS1: E5:E40 WS2: E5:E41 I'm trying to combine the data from both worksheets for a MATCH formula: =MATCH(I15,[WS1!E5:E40,WS2!E5:E41 combined],FALSE) The second argument is obviously what I'm having difficulty with. Any help is greatly appreciated. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Bubbis, I think...

Macro that will add multiple emails based on a range of cell values
I need a macro that will basically look at one column and if it says =93Yes=94 put the email address in the email column in the .bcc. All the email addresses need to be in one email by the way. Here is what my data looks like starting in A4: Yes Job Title email1@work.com No Job Title email2@work.com Yes Job Title email3@work.com No Job Title email4@work.com The list of emails is going to be variable too from month to month. Thanks for the help, Tyson See http://www.rondebruin.nl/sendmail.htm Choose a Outlook example and clik on the tip link http://www.rondebruin.nl/mail/tips2.htm -- ...