EXCEL FORMULA QUESTION

[truitt20]

is it possible to create a formulas like so:

=sumif($A3:$A15000,($C$15004),CORREL($B$13609:$B14446,G$13609:G14446))


where i embed a correlation function within the sumif function?


thanks

 

[SkipVought]

Hi,

SUMIF has 3 arguments...

1 range
2 criteia
3 optional sum range

CORREL returns a VALUE not a range.

TILT!

Skip,
[sub]
[red][/red]
[/sub]

 

[truitt20]

Skip,

Is there a formual that could use the correlation function based on criteria?

thanks

 

[SkipVought]

Please explain what you need to do? I am TOTALLY in the dark!

Skip,
[sub]
[red][/red]
[/sub]

 

[truitt20]

column A has a range of dates
column B has a range of daily returns of the S&P for that specific date
column g has a range of daily returns of our fund for that specific date

i want to run a correlation on columns B & G for specific dates

however, i only want the value of the correlation to be pulled for a specific date (cell C15004). so essentially i want to look for a date within the range A1:A15000 & if that date matches $C$15004 then run the correlation function on the data arrays of B3:B15000,G3:G15000

does that make sense?

 

[SkipVought]

Why not do a query faq68-5829, using the DATE as a criteria.

The do the correlation on the resultset.

Might work with a PivotTable also.

Skip,
[sub]
[red][/red]
[/sub]

 

[Rofeu]

Hi,

I think you can use the following array-formula (enter with Ctrl-Shift-Enter) to accomplish what you're after:

{=CORREL(IF($A$1:$A$15000=$C$15004,$B$1:$B$15000,""),IF($A$1:$A6=$C$15004,$G$1:$G$15000,"")}

Cheers,

Roel

 

[truitt20]

Roel,

Thanks for the help. However, this formula is coming up with a #VALUE error. Any other ways to configure this?

thanks

 

[GlennUK]

Change the $A$6 to $A$15000. Remember to enter using Ctrl-Shift-Enter.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.