Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sergun
 
Posts: n/a
Default Selecting a range of values for another function

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



  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Selecting a range of values for another function

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)


"Sergun" wrote in message
...
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





  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sergun
 
Posts: n/a
Default Selecting a range of values for another function

Hi Bob,

thanks for the reply.

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

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

thanks a lot.
gr. Sergiy



"Bob Phillips" wrote:

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)


"Sergun" wrote in message
...
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






  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Selecting a range of values for another function

There is no A1:A1010. 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)


"Sergun" wrote in message
...
Hi Bob,

thanks for the reply.

The formula you decribed doesn't work... Excel seems to have a problem

with
e.g. A1:A1010 condition...

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

covarif?

thanks a lot.
gr. Sergiy



"Bob Phillips" wrote:

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)


"Sergun" wrote in message
...
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








  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sergun
 
Posts: n/a
Default Selecting a range of values for another function

hm... thanks, I misunderstood.

"Bob Phillips" wrote:

There is no A1:A1010. 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)


"Sergun" wrote in message
...
Hi Bob,

thanks for the reply.

The formula you decribed doesn't work... Excel seems to have a problem

with
e.g. A1:A1010 condition...

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

covarif?

thanks a lot.
gr. Sergiy



"Bob Phillips" wrote:

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)


"Sergun" wrote in message
...
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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing a range name as an argument to the Index Function Michael Sharpe Excel Discussion (Misc queries) 3 September 5th 12 01:33 PM
Cell linked to a range of cell values in different sheet szeng Excel Discussion (Misc queries) 1 August 9th 05 02:41 AM
Error Handling #N/A with AVERAGE Function - Average of values in Row Sam via OfficeKB.com Excel Worksheet Functions 13 July 31st 05 03:59 PM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
How many values appear more than once in a range? Bruce Norris Excel Worksheet Functions 12 April 5th 05 12:44 PM


All times are GMT +1. The time now is 07:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"