Several function questions (nested functions)

[miller]

I am looking for help with nested functions.

I found one on the site that works well for part of what I want to do.
It is:

=SUMPRODUCT(--(A8:A89>=--"2004-11-3"),--(A8:A89<=--"2005-1-1"),D889)

The first change I am looking for is having the value of the date (ex.
2004-11-3) in a seperate cell and the function subs in since I don't
want to change the date in the function everytime I want to change the
date range.

The other question is how would you set up the following functions to
look in that same date range:

=COUNTIF(F8:F89,"W")

=SUM(IF(B8:B89="H",IF(F8:F89="W",1,0)))

=SUMIF(B8:B89,"H",D888)

Here is the data if it helps.




Code:
--------------------
11/3/2004 H DALLAS 91 106 L
11/5/2004 H ORLANDO 89 90 L
11/6/2004 A @ MINNESOTA 92 99 L
11/9/2004 H LA LAKERS 98 106 L
11/12/2004 H ATLANTA 95 96 L
11/13/2004 A @ MILWAUKEE 91 94 L
11/17/2004 H PHOENIX 84 95 L
11/20/2004 H MINNESOTA 94 99 L
11/22/2004 A @UTAH 76 75 W
11/23/2004 A @GOLDEN STATE 109 115 L
11/26/2004 A @PHOENIX 86 95 L
11/28/2004 A @LA LAKERS 76 89 L
11/29/2004 A @DENVER 67 76 L
12/1/2004 H SACRAMENTO 81 94 L
12/4/2004 H DETROIT 69 92 L
12/7/2004 A @HOUSTON 81 89 L
12/8/2004 H NEW YORK 79 86 L
12/10/2004 A @NEW JERSEY 91 94 L
12/12/2004 A @WASHINGTON 69 88 L
12/14/2004 A @CHARLOTTE 93 94 L
12/15/2004 H GOLDEN STATE 98 89 W
12/17/2004 H SAN ANTONIO 67 83 L
12/19/2004 A @SACRAMENTO 71 107 L
12/21/2004 A @LA CLIPPERS 91 100 L
12/22/2004 A @LA LAKERS 89 101 L
12/26/2004 A @CLEVELAND 91 100 L
12/27/2004 A @INDIANA 82 100 L
12/29/2004 H PHOENIX 96 107 L

 

[Biff]

Hi!

Use 2 cells to hold the date boundaries:

A1 = 11/3/2004
B1 = 1/1/2005

For Total Points:

=SUMPRODUCT(--(A$8:A$89>=A$1),--(A$8:A$89<=B$1),D$8$89)

For # of Wins:

=SUMPRODUCT(--(A$8:A$89>=A$1),--(A$8:A$89<=B$1),--(F$8:F$89="W"))

For Home Wins:

=SUMPRODUCT(--(A$8:A$89>=A$1),--(A$8:A$89<=B$1),--(B$8:B$89="H"),--(F$8:F$89="W"))

For Home Points:

=SUMPRODUCT(--(A$8:A$89>=A$1),--(A$8:A$89<=B$1),--(B$8:B$89="H"),D$8$89)

I don't know what team that data is for but for that time period they sure
did suck!

Biff

 

[miller]

Biff,

Works like a dream. THANK YOU VERY MUCH!

The team was New Orleans last year and ya they did suck.

Mille

 

[miller]

Anyway you can write a function for # of wins and total points that
does the same for the last 7 games.

So it would the end date + the 6 cells before it. From the data above:

The end is 11/29/2004 the function would come back:

1 for wins and 592 for total points scored.

 

[Biff]

Hi!

A1 = 11/29/2004
B1 = 7
C1 = W

Total points for the last n games (based on the date entered in A1 and the
value enterd in B1):

=SUM(OFFSET(D8,MATCH(A1,A$8:A$89,0)-1,,-B1))

Number of wins using the same criteria:

=COUNTIF(OFFSET(F8,MATCH(A1,A$8:A$89,0)-1,,-B1),C1)

Biff

 

[miller]

Biff,

YOU RULE!! Thank you for all your help.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff