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 cant 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),"")

=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),"")

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),"")

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),"")

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),"")

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),"")

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),"")