| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Several function questions (nested functions)
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"),D8:D89) 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",D8:D88) 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 -------------------- -- miller ------------------------------------------------------------------------ miller's Profile: http://www.excelforum.com/member.php...fo&userid=4238 View this thread: http://www.excelforum.com/showthread...hreadid=474550 |
|
#2
|
|||
|
|||
|
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:D$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:D$89) I don't know what team that data is for but for that time period they sure did suck! Biff "miller" wrote in message ... 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"),D8:D89) 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",D8:D88) 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 -------------------- -- miller ------------------------------------------------------------------------ miller's Profile: http://www.excelforum.com/member.php...fo&userid=4238 View this thread: http://www.excelforum.com/showthread...hreadid=474550 |
|
#3
|
|||
|
|||
|
Biff, Works like a dream. THANK YOU VERY MUCH! The team was New Orleans last year and ya they did suck. Miller -- miller ------------------------------------------------------------------------ miller's Profile: http://www.excelforum.com/member.php...fo&userid=4238 View this thread: http://www.excelforum.com/showthread...hreadid=474550 |
|
#4
|
|||
|
|||
|
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. -- miller ------------------------------------------------------------------------ miller's Profile: http://www.excelforum.com/member.php...fo&userid=4238 View this thread: http://www.excelforum.com/showthread...hreadid=474550 |
|
#5
|
|||
|
|||
|
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" wrote in message ... 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. -- miller ------------------------------------------------------------------------ miller's Profile: http://www.excelforum.com/member.php...fo&userid=4238 View this thread: http://www.excelforum.com/showthread...hreadid=474550 |
|
#6
|
|||
|
|||
|
Biff, YOU RULE!! Thank you for all your help. :) -- miller ------------------------------------------------------------------------ miller's Profile: http://www.excelforum.com/member.php...fo&userid=4238 View this thread: http://www.excelforum.com/showthread...hreadid=474550 |
|
#7
|
|||
|
|||
|
You're welcome. Thanks for the feedback!
Biff "miller" wrote in message ... Biff, YOU RULE!! Thank you for all your help. :) -- miller ------------------------------------------------------------------------ miller's Profile: http://www.excelforum.com/member.php...fo&userid=4238 View this thread: http://www.excelforum.com/showthread...hreadid=474550 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| nested if(and) functions | Excel Discussion (Misc queries) | |||
| Round a number in nested function | Excel Worksheet Functions | |||
| color of a cell within a nested if function | Excel Discussion (Misc queries) | |||
| Function Macro for Nested IF | Excel Worksheet Functions | |||
| Combining SUM Function with Nested If Statement | Excel Discussion (Misc queries) | |||
Linear Mode
