# Conditional Square Of Pearson Product Moment Correlation Coefficient

Jan 3, 2010

I have a very large spreadsheet, a few columns and rows I attach..

in col a we have the time of the event..in column b i have a numerical rank for the
importance of factor 1,in column c i have similiar for factor 2.

What I want is the correlation for each event between col b and col c using the rsq function..i have no problem using this function..but i need the result for each event produced in col d in this same sheet and printed on each row and since I have thousand of rows i need a formula I can fill down

see attached sheet

## Return Correlation Coefficient Of 2 Ranges

Jul 23, 2007

I have the monthly data stores column by column, e.g. Column B for Jan 07, Col C for Feb 07 etcetc, so every month I have a new column added to the end then I have some statistical analysis table which calculates such as annual return, standard deviation etcetc

How can I make a button which I can click then it can update to replace the formular with the latest column? for example, =CORREL(B2:B10, G2:G10), col B contains Feb 07 data and G contains June 07, let's say I add another column for July 07 which will be column H and I would like by clicking the button then all formula with col G will be replaced by col H?

## Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)

Sep 30, 2009

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace �PRODUCT� with a �SUM�.

## Finding Whether A Number Is A Square, Or Whether Square Root Is Integer

Aug 19, 2009

I need a formula to tell me whether a number is a square number, or I can do it so to see if the square root of a number is an interger. So far Iv been doing

A1 = ��2
A2 = AND(RIGHT(A1,1)=INT(A1))

True or false outcome is fine, and it has been working fine on some examples, but the problem comes when I have ��49, as it is 2 digits long. I've seen a formula similar to find the root symbol, and look at all the numbers upto this point (maybe FIND).

## Conditional Product Of Sum

Mar 3, 2009

Maybe I should say conditional product of conditional sum. Eitherway, the values in column J are summed if P is column R for the repective row.

## Sum Figure In Conditional Product And Year

May 27, 2014

What's the functions can figure out on the below conditions, For example :

PRODUCTSYEARQTY
AAA 2015 2
ABB 2011 3
AAA 2013 8
BBA 2011 6
AAA 2011 4
BCC 2014 1

Conditions:
1) looking for AAA product
2) sum the qty is before 2014

## Product If Function For Conditional Range

Oct 30, 2013

Currently trying to calculate the cumulative performance for a range of months. For example, I have the below figures:

Jul-12
6.59%

Aug-12
1.13%

Sep-12
2.61%

Oct-12
-3.56%

[code].....

I want to create a product if function where if I'm looking back 6, 12, 18 months etc from a specified month (e.g. Sep 2013), it will calculate the product for the range of % (e.g. Apr-Sep for 6 months, Oct 12-Sep 13 for 12 months, etc...). I would imagine it will be =product(1+if(Column A range=6, Column B range))-1.

## Trying To Create A Conditional Product Function In XL2003

Sep 4, 2008

I'm trying to do:

Column A is full of daily consecutive dates, Columns B to H are full of numbers.

I'd like to create function that accepts 2 dates and a column reference as input like this:

function(date1, date2, columnnumber)

And as output it gives the product of all the numbers in that referenced column between the two dates.

## StringConcat (Chip Pearson) Calculation Delay

Oct 20, 2009

I have used Chip's StringConcat code [url]

When I first entered the function code as a formula on the worksheet, the result was instant and correct.

However, if I change the contents of one of the cells in the stringconcat range, the result does not change.

I need to edit (eg F2 then enter) either the cell with the text or the cell with the formula for the result to update.
If I highlight the column where the formula is used and Find / Replace to change "=" for "=", they all update at once (tip I learn't here)

Has anyone experienced this or have any ideas why it may happen?

Notes:
I am using Excel 2000 (work)
I have 6 cols of info (I to N),
These 6 columns contain formulae that basically copies plain text from the same row in Cols A to F. (Since not all the left hand side cells are full, the formula copies down the value above if the cell isblank, or copies the cell value if there istext)
Formula is in Cell G2 and reads
=stringconcat("; ",TRUE,I2:N2)
Code is stored in the Module1
I have 1 header row (Row 1)
I have 16 rows of text (Rows 2 to 17)
Not all rows have text in all 6 columns, but the are no blank cells betweeen data working left to right
I need our IT Admin to install Excel Genie!

## Excel For Data Aggregation And Then Disaggregation Using Coefficient

Oct 9, 2011

I have a problem in Excel for Supply Chain. I want to aggregate a set of data, change the aggregated data and then disaggregate back to constituents using the same proportion in which they were aggregated . I can do aggregation using pivot tables but then I cannot change and do the deaggregation.

Initial Disaggregated data
--------------------
Group Item# Qty
BALL423242254BALL534534555BALL4232422442BALL543535433
BALL534535322BAT53453532BAT5343533222BAT535353222
CYCLE432424222CYCLE4234244222CYCLE43243241313
CYCLE42342257676CYCLE43244226565

Aggregated data
-----------
BALL606BAT246CYCLE15798

Change in Disaggregated data
-------------------------
BALL1000BAT3000CYCLE5000

After the change, I should be able to disaggregate the data using the same co-efficient I used in aggregation

## Dynamic Named Range For The Product Name On The Product Details Sheet

Dec 24, 2008

I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.

So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.

The drop down list displays the products and can be added to by typing new product names on the other sheet.

What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.

## Macro For Name Generation From Product To Product With Sizes And Filenames

Jan 30, 2014

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

## Displaying Date And Time At The Moment Of Entry

Dec 12, 2011

I have cells on my spreadsheet which contain: =now() and they work as they should - displaying the date and time at the moment of entry. Unfortunately the cells update when other info is added at other locations on the sheet - is there a way to ensure they enter the current 'now' only at the point of initial entry - such that the data doesn't then alter?

## Formula That Reads Off A Single Cell At The Moment

Sep 17, 2009

i have a formula that reads off a single cell at the moment.

=IF(N38="MB","KL","MB") this works fine. I need the this to read off a range of cells from N38 to N43.

## Product Look Up: Look Up A Product Based On The Number And The Qty

Feb 20, 2009

i need to look up a product based on the number and the qty. see the example attached.

On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.

## Type The Product Id In The A Column Of The Orders Sheet(Sheet2) To Auto Insert The Product Name In The B Column

Jul 16, 2006

In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.

## Cell Referencing: Enter A Product Name In One Cell And Have The Price For That Product Automatically Appear In Another Cell

Feb 3, 2009

I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?

## How To Use Correlation In Vba

Feb 25, 2008

concerning the use of correlation in vba. What are my options when i want to calculate a correlation within vba. The variables are all still within vba.

Who knows what formula's i could use to keep everything within vba.

If you don't understand my question please ask so i can try to make it more clear.

## Correlation Matrix In VBA

Jan 16, 2006

I have code to create a correlation matrix (NxN, where N is the number of columns). This is done by selecting an area that is NxN, entering the function and range, then hitting ctrl +shift + enter (array formula).

However, I want to convert this to accept VBA arrays, rather than a data range, and give the output in form of an array as well.

VB:
Function CorrmatK(dataRange As Object) As Variant
On Error Goto 20
Dim r As Integer, n As Integer, rr As Integer, i As Integer, j As Integer, k As Integer, doit As Integer
Dim x() As Variant, mc() As Double, ss() As Double, m() As Double, ob As Object
r = dataRange.Rows.Count
n = dataRange.Columns.Count

[Code] ......

## Significance Of Correlation

Oct 30, 2006

I am in the unfortunate position of having to run about 900 correlations with Excel as my only option. Normally this would be fine, but in this case, I need the Pearson's test of significance. The only way I know how to obtain this in Excel is through using a regression, which is incredibly time-consuming. Does anyone have a suggestion how to make the best use of time?

## Correlation Between Two Different Distributions

Mar 11, 2014

I am using excel to do a monte carlo analysis of a process. Until now, all my distributions have been uncorrelated, but from now I would like to produce distributions with a given correlation. This is a fairly easy process for normal distribution, but in this case one of the distributions is Weibull and the other lognormal. I don't have a stats background, so how to implement this. Producing the two distributions is no problem, but I would now like to be able to alter them or the original formulae so they are correlated properly. If it would greatly simplify the process, the lognormal distribution could be replaced by a normal, but not the weibull.

The formulae I'm using for each is:

## Correlation Matrix

Jul 25, 2006

I wasn't able to attach the file because it was too big, but you can download it from here www.easygcc.com/correl.rar. On the sheet called " Correlation" there is matrix, I am baiscally trying to fill in the correlation formula into every cell so the matrix is filled out. The data for the correlation calculation comes from the sheet called "Tech Data". I have filled in the correlation formula into a few of the cells as an example, but I don't want to continue doing this manually but rather have a macro do it for me. Otherwise this will take for ever. My macro is also a part of the file, if you would liek to look at it and maybe fix what I already have.

## Punnet Square

Aug 15, 2006

I am doing a cross between two parents, whose possible genotypes are AA, BB, or AB.
I want to use if clauses to look at the parent genotypes, (paternal is listed in column E and maternal is listed in column F) and then give me the possible recombinant genotypes in column G. there are 9 possible combinations (recombinants in parenthesis)

AA x AA (AA)
BB x BB (BB)
AA x AB (AA, AB)
AB x AA (AA, AB)
BB x AB (BB, AB)
AB x BB (BB, AB)
AB x AB (AA, AB, BB)
AA x BB (AB)
BB x AA (AB)

I tried using if clauses, but i don't think i can nest more than 7 cases, and I have 9 different combos.

## Dynamic Correlation Matrices

Sep 15, 2014

I have a table that I add rows to via a macro. I am trying to add correlation matrices that find the last row of the table, and include a certain number of rows above that to create a rolling correlation. For example, if I want the 2 week correlation, I would like the last 10 rows of data, but if I add another row, this formula should move down to incorporate the new row and deselect the now 11th day preceding. I do this manually but it is very time consuming.

## Correlation Between Data In Columns

Mar 3, 2014

I am now analysing the ordered correlations of products in a warehouse.For example, I have 16 products (product 1 to product 16) in my warehouse. During last month, there were totally 391 orders (order 1 to order 391). If a product is requested in a ordered, an "X" will be marked as per attached sheet.

Now my goal is to figure out which pair (or even triple) of products are ordered/requested at the same time (or in the same order) for the last month. I intend to use the analasys result to move the more ordered pair/triple products to stay near to each other at the warehouse so it will save the time for picking.

How to do that analysis?

## Index - All Up All Square Feet

Mar 13, 2014

I have 6 rows A1-A6(sheetsize), they can be 1 of 8 numbers. (16,14,12,10, 5/8, 1/2, 3/8, 3/16) these are all metal gauges. The cell next to them B1-B6(sheetgauge) has a number that refers to how much sq ft each of these sheet metal sizes has. I would like a cell below A30 to add up all the sq ft in B1-B6 if it is 16 gauge. Then A31 will add up all the 14 gauge, etc.

How is this possible without having a huge formula.

Here is my standard formula, but it wont add the B cells together if more than one of the same sheet sizes.

=index(sheetsize,match(A31,sheetgauge,0))

## Dynamic Ranges For Correlation

Nov 18, 2009

I have a particular issue - i am trying to create a rolling correlation.

lets say you have two variables for 3 different months:

Month1 a x
Month2 b y
Month3 c z

correlation will correlate a-c with t-v

however, come next month - i have to manually drag the ranges for a rolling three month correlation:

Month1 a t
Month2 b u
Month3 c v
Month4 d w

correlation will now correlate b-d with u-w for a the last three months.

I want to automate this as I know what the month is, I can't seem to return the last three months and correlate the data.

I have tried to do this with the database functions, but I think that they break the array relationship of b-d and u-w in the correlation function.

## Use Of Square Brackets In Vba 2003

Jan 8, 2010

My code works great on my computer but when I bring it to another computer it gives me errors. I think it has to do with my use of square brackets to reference cells. the error that comes up is

compile error:
can't find project or library

What I'm doin here is going to my template sheet then getting the info from different cells and then later using it somewhere else. in place of using range and selecting cell I am using square brackets to select a cell. it worked fine on my computer and was a lot less writing, here is a small part of my code

## Correlation Tables -- Setup

Oct 17, 2005

I'm trying to Pearson Ranks correlation table. This is the formula I'm
using:

=PEARSON(Ranks!\$B\$3:\$B\$230,Ranks!B\$3:B\$230)

This is placed in cell B3. When I copy the formula over to DT3 the formula
correctly adjusts the B in the second part of the formula all the way to DT
and still references the values in the B column.

But I want to now copy this down to C4, D5, E6 and then over to DT for each
so that it correctly makes the correlation table.

## Correlation Between Random Numbers

Dec 17, 2006

I wonder if you think it can be possible to constraint the RAND function so that it gives off its result commensurate with the result of another RAND function with certain probability N? For example,

Say there are two random functions and the first generates 0,5667 the correlation is at 0,5 so there is a 50% probability that the second random function will generate the result very close to it, say, 0,6012�.Do you think the second random function can be tweaked so that it performs in this manner�.when there is a 100% correlation � the results of both random functions will agree�when it is 80% it will be very close�..do you think this can be done with NORMINV?