If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
I am trying to get this formula to average the values in row I for all
individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can’t seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#2
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
=SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw
Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#3
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
Thanks for the assistance. The formula resolution you offered however only
evaluates the formula, and returns a true statement. It did not average the values I input inot Row I however. Did I miss something? "T. Valko" wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#4
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
The formula resolution you offered however only
evaluates the formula, and returns a true statement. =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) I have no idea what you mean by that? Do you mean the formula returned the logical value TRUE? I don't see how that's possible. Did you enter the formula as an array? -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... Thanks for the assistance. The formula resolution you offered however only evaluates the formula, and returns a true statement. It did not average the values I input inot Row I however. Did I miss something? "T. Valko" wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#5
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
When I entered your formula as an array, the result in the cell merely said
"True". What I am attempting to do, is have the formula average all numerical values in Column I, for those individuals who were admitted in 2008 ONLY. If an individual was admitted in 2009, than the values posted in Column I (for those individuals) should not be averaged in. I plan to average the Column I values for 2009 admissions separately from 2008. The other part of the formula just advises to leave the outcome blank, if no values are posted in Column I, and no dates posted in Column C (the spreadsheet is currently blank, as I haven't begun to enter data yet). Again, thanks very much Biff! Dan "T. Valko" wrote: The formula resolution you offered however only evaluates the formula, and returns a true statement. =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) I have no idea what you mean by that? Do you mean the formula returned the logical value TRUE? I don't see how that's possible. Did you enter the formula as an array? -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... Thanks for the assistance. The formula resolution you offered however only evaluates the formula, and returns a true statement. It did not average the values I input inot Row I however. Did I miss something? "T. Valko" wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#6
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
When I entered your formula as an array, the result in the cell merely said
"True". What I am attempting to do, is have the formula average all numerical values in Column I, for those individuals who were admitted in 2008 ONLY. If an individual was admitted in 2009, than the values posted in Column I (for those individuals) should not be averaged in. I plan to average the Column I values for 2009 admissions separately from 2008. The other part of the formula just advises to leave the outcome blank, if no values are posted in Column I, and no dates posted in Column C (the spreadsheet is currently blank, as I haven't begun to enter data yet). Again, thanks very much Biff! Dan "T. Valko" wrote: The formula resolution you offered however only evaluates the formula, and returns a true statement. =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) I have no idea what you mean by that? Do you mean the formula returned the logical value TRUE? I don't see how that's possible. Did you enter the formula as an array? -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... Thanks for the assistance. The formula resolution you offered however only evaluates the formula, and returns a true statement. It did not average the values I input inot Row I however. Did I miss something? "T. Valko" wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#7
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
Biff's formula has to work
Let's try an experiment on a new sheet In A1:B5 enter data like this 01/01/2009 1 02/02/2008 2 01/04/2009 3 04/05/2008 4 03/05/2009 5 In some cell like D1 enter =AVERAGE(IF(YEAR(A1:A5)=2009,B1:B5)) and, since it is an array formula, commit it with CTRL+SHIFT+ENTER not just ENTER. Excel will place the formula within braces and give the correct result (here 3) Can you get this to work? Now try on you actual worksheet best wsihes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Danny Boy" wrote in message ... When I entered your formula as an array, the result in the cell merely said "True". What I am attempting to do, is have the formula average all numerical values in Column I, for those individuals who were admitted in 2008 ONLY. If an individual was admitted in 2009, than the values posted in Column I (for those individuals) should not be averaged in. I plan to average the Column I values for 2009 admissions separately from 2008. The other part of the formula just advises to leave the outcome blank, if no values are posted in Column I, and no dates posted in Column C (the spreadsheet is currently blank, as I haven't begun to enter data yet). Again, thanks very much Biff! Dan "T. Valko" wrote: The formula resolution you offered however only evaluates the formula, and returns a true statement. =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) I have no idea what you mean by that? Do you mean the formula returned the logical value TRUE? I don't see how that's possible. Did you enter the formula as an array? -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... Thanks for the assistance. The formula resolution you offered however only evaluates the formula, and returns a true statement. It did not average the values I input inot Row I however. Did I miss something? "T. Valko" wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#8
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
So I have it working, however, the only other thing I need to account for (to
avoid DIV/0 and #Value! errors) is for the formula to ignore doing its calculations when the admission dates in Column C are either blank, or outside of the correct year paramaters of the formula (e.g. in your example Bernard, 2009). Thus, any outcome number in Column I would be ignored, if admission dates were from 2008, 2006, etc. As a test on a blank spredsheet, I entered two dates in 2007 (Column C), and two outcome numbers in Column H, and I received the DIV/0 error. If however I add a 2009 date, the formula works. As opposed to the DIV/) error, I would like the outcome to just leave the cell "blank", until the correct dates (e.g. 2009) are included on the spreadsheet. Here is what I added in an attempt to account for the above, but it would not seem to work: =AVERAGE(IF(YEAR('Raw Data'!C2:C50000)=2009,'Raw Data'!I2:I5000,AND('Raw Data'!C2:C5000"",AND('Raw Data'!I2:I5000"")))) "Bernard Liengme" wrote: Biff's formula has to work Let's try an experiment on a new sheet In A1:B5 enter data like this 01/01/2009 1 02/02/2008 2 01/04/2009 3 04/05/2008 4 03/05/2009 5 In some cell like D1 enter =AVERAGE(IF(YEAR(A1:A5)=2009,B1:B5)) and, since it is an array formula, commit it with CTRL+SHIFT+ENTER not just ENTER. Excel will place the formula within braces and give the correct result (here 3) Can you get this to work? Now try on you actual worksheet best wsihes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Danny Boy" wrote in message ... When I entered your formula as an array, the result in the cell merely said "True". What I am attempting to do, is have the formula average all numerical values in Column I, for those individuals who were admitted in 2008 ONLY. If an individual was admitted in 2009, than the values posted in Column I (for those individuals) should not be averaged in. I plan to average the Column I values for 2009 admissions separately from 2008. The other part of the formula just advises to leave the outcome blank, if no values are posted in Column I, and no dates posted in Column C (the spreadsheet is currently blank, as I haven't begun to enter data yet). Again, thanks very much Biff! Dan "T. Valko" wrote: The formula resolution you offered however only evaluates the formula, and returns a true statement. =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) I have no idea what you mean by that? Do you mean the formula returned the logical value TRUE? I don't see how that's possible. Did you enter the formula as an array? -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... Thanks for the assistance. The formula resolution you offered however only evaluates the formula, and returns a true statement. It did not average the values I input inot Row I however. Did I miss something? "T. Valko" wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#9
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
What version of Excel are you using?
If you're using Excel 2007. Still array entered: =IFERROR(AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2009,'Raw Data'!I2:I5000)),"") For other versions of Excel. Still array entered: =IF(SUM(--(YEAR('Raw Data'!C2:C5000)=2009)),AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2009,'Raw Data'!I2:I5000)),"") -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... So I have it working, however, the only other thing I need to account for (to avoid DIV/0 and #Value! errors) is for the formula to ignore doing its calculations when the admission dates in Column C are either blank, or outside of the correct year paramaters of the formula (e.g. in your example Bernard, 2009). Thus, any outcome number in Column I would be ignored, if admission dates were from 2008, 2006, etc. As a test on a blank spredsheet, I entered two dates in 2007 (Column C), and two outcome numbers in Column H, and I received the DIV/0 error. If however I add a 2009 date, the formula works. As opposed to the DIV/) error, I would like the outcome to just leave the cell "blank", until the correct dates (e.g. 2009) are included on the spreadsheet. Here is what I added in an attempt to account for the above, but it would not seem to work: =AVERAGE(IF(YEAR('Raw Data'!C2:C50000)=2009,'Raw Data'!I2:I5000,AND('Raw Data'!C2:C5000"",AND('Raw Data'!I2:I5000"")))) "Bernard Liengme" wrote: Biff's formula has to work Let's try an experiment on a new sheet In A1:B5 enter data like this 01/01/2009 1 02/02/2008 2 01/04/2009 3 04/05/2008 4 03/05/2009 5 In some cell like D1 enter =AVERAGE(IF(YEAR(A1:A5)=2009,B1:B5)) and, since it is an array formula, commit it with CTRL+SHIFT+ENTER not just ENTER. Excel will place the formula within braces and give the correct result (here 3) Can you get this to work? Now try on you actual worksheet best wsihes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Danny Boy" wrote in message ... When I entered your formula as an array, the result in the cell merely said "True". What I am attempting to do, is have the formula average all numerical values in Column I, for those individuals who were admitted in 2008 ONLY. If an individual was admitted in 2009, than the values posted in Column I (for those individuals) should not be averaged in. I plan to average the Column I values for 2009 admissions separately from 2008. The other part of the formula just advises to leave the outcome blank, if no values are posted in Column I, and no dates posted in Column C (the spreadsheet is currently blank, as I haven't begun to enter data yet). Again, thanks very much Biff! Dan "T. Valko" wrote: The formula resolution you offered however only evaluates the formula, and returns a true statement. =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) I have no idea what you mean by that? Do you mean the formula returned the logical value TRUE? I don't see how that's possible. Did you enter the formula as an array? -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... Thanks for the assistance. The formula resolution you offered however only evaluates the formula, and returns a true statement. It did not average the values I input inot Row I however. Did I miss something? "T. Valko" wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
#10
|
|||
|
|||
Formula problems that keep producing a #Value! Error!!
I'm using Excel 2007 (which I just began using yesterday after years of
2003). That additional piece of formula you added in Biff (IFERROR) did the trick......THANK YOU SO MUCH. Now it works just perfectly. Sorry for any lack of clarity on my part which resulted in so much back and forth. But I very much appreciate the time you and Bernard took. The Excel Discussion Group has saved my life on more than one occassion...... Best, Dan "T. Valko" wrote: What version of Excel are you using? If you're using Excel 2007. Still array entered: =IFERROR(AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2009,'Raw Data'!I2:I5000)),"") For other versions of Excel. Still array entered: =IF(SUM(--(YEAR('Raw Data'!C2:C5000)=2009)),AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2009,'Raw Data'!I2:I5000)),"") -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... So I have it working, however, the only other thing I need to account for (to avoid DIV/0 and #Value! errors) is for the formula to ignore doing its calculations when the admission dates in Column C are either blank, or outside of the correct year paramaters of the formula (e.g. in your example Bernard, 2009). Thus, any outcome number in Column I would be ignored, if admission dates were from 2008, 2006, etc. As a test on a blank spredsheet, I entered two dates in 2007 (Column C), and two outcome numbers in Column H, and I received the DIV/0 error. If however I add a 2009 date, the formula works. As opposed to the DIV/) error, I would like the outcome to just leave the cell "blank", until the correct dates (e.g. 2009) are included on the spreadsheet. Here is what I added in an attempt to account for the above, but it would not seem to work: =AVERAGE(IF(YEAR('Raw Data'!C2:C50000)=2009,'Raw Data'!I2:I5000,AND('Raw Data'!C2:C5000"",AND('Raw Data'!I2:I5000"")))) "Bernard Liengme" wrote: Biff's formula has to work Let's try an experiment on a new sheet In A1:B5 enter data like this 01/01/2009 1 02/02/2008 2 01/04/2009 3 04/05/2008 4 03/05/2009 5 In some cell like D1 enter =AVERAGE(IF(YEAR(A1:A5)=2009,B1:B5)) and, since it is an array formula, commit it with CTRL+SHIFT+ENTER not just ENTER. Excel will place the formula within braces and give the correct result (here 3) Can you get this to work? Now try on you actual worksheet best wsihes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Danny Boy" wrote in message ... When I entered your formula as an array, the result in the cell merely said "True". What I am attempting to do, is have the formula average all numerical values in Column I, for those individuals who were admitted in 2008 ONLY. If an individual was admitted in 2009, than the values posted in Column I (for those individuals) should not be averaged in. I plan to average the Column I values for 2009 admissions separately from 2008. The other part of the formula just advises to leave the outcome blank, if no values are posted in Column I, and no dates posted in Column C (the spreadsheet is currently blank, as I haven't begun to enter data yet). Again, thanks very much Biff! Dan "T. Valko" wrote: The formula resolution you offered however only evaluates the formula, and returns a true statement. =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) I have no idea what you mean by that? Do you mean the formula returned the logical value TRUE? I don't see how that's possible. Did you enter the formula as an array? -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... Thanks for the assistance. The formula resolution you offered however only evaluates the formula, and returns a true statement. It did not average the values I input inot Row I however. Did I miss something? "T. Valko" wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") The ranges have to be the same size. Try this array formula** : =AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... I am trying to get this formula to average the values in row I for all individuals seen during the year 2008. The formula also acknowledges that it should ignore any cell in row I, if it is blank. For some reason I can't seem to get the formula to work, and I keep getting a #VALUE! error. Any suggestions? Thanks, Dan Below is the formula I originally wrote: =SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw Data'!$I$2:$I5000)0,AVERAGE('Raw Data'!$I$2: $I5000),"") |
|
Thread Tools | |
Display Modes | |
|
|