I have a worksheet with ~10 columns and a 1000 rows.

In column F, I have a date that has a range of several years.
In column H I have a value, which sometimes is 0 or negative.

I need two formulas:

1) I would like to create a formula that would take the average value (from
column H) providing it was greater than or equal to 0 for a given year. So
for 2007 the average value is, say 10. For 2008, 12, etc....

2) I would like the second formula, simple count the n that went into
determining the average.

Try these:

For the average...

Array entered**

=AVERAGE(IF((YEAR(F1:F1000)=2008)*(H1:H1000=0),H1 :H1000))

** 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.

For the count...

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(H1:H1000=0))

Note that in both formulas empty cells will evaluate to be =0. So if you do
have empty cells you'll have to account for them to be excluded.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have a worksheet with ~10 columns and a 1000 rows.

In column F, I have a date that has a range of several years.
In column H I have a value, which sometimes is 0 or negative.

I need two formulas:

1) I would like to create a formula that would take the average value
(from
column H) providing it was greater than or equal to 0 for a given year.
So
for 2007 the average value is, say 10. For 2008, 12, etc....

2) I would like the second formula, simple count the n that went into
determining the average.

I do have blanks, so I will have to use IFERROR?

"T. Valko" wrote:

Try these:

For the average...

Array entered**

=AVERAGE(IF((YEAR(F1:F1000)=2008)*(H1:H1000=0),H1 :H1000))

** 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.

For the count...

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(H1:H1000=0))

Note that in both formulas empty cells will evaluate to be =0. So if you do
have empty cells you'll have to account for them to be excluded.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have a worksheet with ~10 columns and a 1000 rows.

In column F, I have a date that has a range of several years.
In column H I have a value, which sometimes is 0 or negative.

I need two formulas:

1) I would like to create a formula that would take the average value
(from
column H) providing it was greater than or equal to 0 for a given year.
So
for 2007 the average value is, say 10. For 2008, 12, etc....

2) I would like the second formula, simple count the n that went into
determining the average.

Just add a test for a number in the cell:

Average (still array entered):

=AVERAGE(IF((YEAR(F1:F1000)=2008)*(ISNUMBER(H1:H10 00))*(H1:H1000=0),H1:H1000))

Count:

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(ISNUMBER(H1:H1000)),--(H1:H1000=0))


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I do have blanks, so I will have to use IFERROR?

"T. Valko" wrote:

Try these:

For the average...

Array entered**

=AVERAGE(IF((YEAR(F1:F1000)=2008)*(H1:H1000=0),H1 :H1000))

** 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.

For the count...

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(H1:H1000=0))

Note that in both formulas empty cells will evaluate to be =0. So if you
do
have empty cells you'll have to account for them to be excluded.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have a worksheet with ~10 columns and a 1000 rows.

In column F, I have a date that has a range of several years.
In column H I have a value, which sometimes is 0 or negative.

I need two formulas:

1) I would like to create a formula that would take the average value
(from
column H) providing it was greater than or equal to 0 for a given year.
So
for 2007 the average value is, say 10. For 2008, 12, etc....

2) I would like the second formula, simple count the n that went into
determining the average.

As a test I did a small range and it worked fine:

=AVERAGE(IF((YEAR(F2739:F2741)=2008)*(ISNUMBER(H27 39:H2741))*(H2739:H2741=0),H2739:H2741)).

Then I added a row, which was blank and I got the "#Value" error.

=AVERAGE(IF((YEAR(F2738:F2741)=2008)*(ISNUMBER(H27 38:H2741))*(H2738:H2741=0),H2738:H2741))

Formatting was checked and both were arrays.

"T. Valko" wrote:

Just add a test for a number in the cell:

Average (still array entered):

=AVERAGE(IF((YEAR(F1:F1000)=2008)*(ISNUMBER(H1:H10 00))*(H1:H1000=0),H1:H1000))

Count:

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(ISNUMBER(H1:H1000)),--(H1:H1000=0))


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I do have blanks, so I will have to use IFERROR?

"T. Valko" wrote:

Try these:

For the average...

Array entered**

=AVERAGE(IF((YEAR(F1:F1000)=2008)*(H1:H1000=0),H1 :H1000))

** 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.

For the count...

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(H1:H1000=0))

Note that in both formulas empty cells will evaluate to be =0. So if you
do
have empty cells you'll have to account for them to be excluded.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have a worksheet with ~10 columns and a 1000 rows.

In column F, I have a date that has a range of several years.
In column H I have a value, which sometimes is 0 or negative.

I need two formulas:

1) I would like to create a formula that would take the average value
(from
column H) providing it was greater than or equal to 0 for a given year.
So
for 2007 the average value is, say 10. For 2008, 12, etc....

2) I would like the second formula, simple count the n that went into
determining the average.

(YEAR(F2738:F2741)=2008)

Are there any TEXT entries in that range? Are there formulas in that range
that return formula blanks ("") ?

Try replacing the above expression with this one:

(TEXT(F2738:F2741,"yyyy")="2008")

Don't forget to array enter!

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
As a test I did a small range and it worked fine:

=AVERAGE(IF((YEAR(F2739:F2741)=2008)*(ISNUMBER(H27 39:H2741))*(H2739:H2741=0),H2739:H2741)).

Then I added a row, which was blank and I got the "#Value" error.

=AVERAGE(IF((YEAR(F2738:F2741)=2008)*(ISNUMBER(H27 38:H2741))*(H2738:H2741=0),H2738:H2741))

Formatting was checked and both were arrays.

"T. Valko" wrote:

Just add a test for a number in the cell:

Average (still array entered):

=AVERAGE(IF((YEAR(F1:F1000)=2008)*(ISNUMBER(H1:H10 00))*(H1:H1000=0),H1:H1000))

Count:

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(ISNUMBER(H1:H1000)),--(H1:H1000=0))


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I do have blanks, so I will have to use IFERROR?

"T. Valko" wrote:

Try these:

For the average...

Array entered**

=AVERAGE(IF((YEAR(F1:F1000)=2008)*(H1:H1000=0),H1 :H1000))

** 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.

For the count...

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(H1:H1000=0))

Note that in both formulas empty cells will evaluate to be =0. So if
you
do
have empty cells you'll have to account for them to be excluded.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have a worksheet with ~10 columns and a 1000 rows.

In column F, I have a date that has a range of several years.
In column H I have a value, which sometimes is 0 or negative.

I need two formulas:

1) I would like to create a formula that would take the average
value
(from
column H) providing it was greater than or equal to 0 for a given
year.
So
for 2007 the average value is, say 10. For 2008, 12, etc....

2) I would like the second formula, simple count the n that went
into
determining the average.