Selecting a range of values for another function

[Guest]

Hi,

I cannot seem to figure this one out...

I have a table that contains e.g.:
Company1 Company2 Company3
Date1 Value11 Value21
Date2 Value12 Value22
Date3 Value13 Value23

etc...

For a function e.g. COVAR i want to select the values that are between
GivenDate1 and GivenDate1-30, for a certain company. The results should be
placed into following table:

Company1 GivenDate1 COVAR
GivenDate2 COVAR
Company2 GivenDate1 COVAR
GivenDate2 COVAR

How on earth should I tackle this? Any help would be really appreciated.

gr. Sergiy

 

[Bob Phillips]

You can get the detail arrays with something like

=IF((A2:A100>=--"2005-11-01")*(A2:A100<=--"2005-11-30")*(B1:M1="Company1")),
B2:M100)

as an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob,

thanks for the reply.

The formula you decribed doesn't work... Excel seems to have a problem with
e.g. A1:A10>10 condition...

is there a way to create a custom formula, similar to sumif, but e.g. covarif?

thanks a lot.
gr. Sergiy

 

[Bob Phillips]

There is no A1:A10>10. This is the way to have multiple conditions, and I
was giving you an example to incorporate in your COVAR formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

hm... thanks, I misunderstood.

There is no A1:A10>10. This is the way to have multiple conditions, and I
was giving you an example to incorporate in your COVAR formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)