conditional formula

  • Thread starter Thread starter PAL
  • Start date Start date
P

PAL

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.
 
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.
 
I do have blanks, so I will have to use IFERROR?

T. Valko said:
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.
Click to expand...
 
I do have blanks, so I will have to use IFERROR?

T. Valko said:
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.
Click to expand...
 
Just add a test for a number in the cell:

Average (still array entered):

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

Count:

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(ISNUMBER(H1:H1000)),--(H1:H1000>=0))
 
Just add a test for a number in the cell:

Average (still array entered):

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

Count:

=SUMPRODUCT(--(YEAR(F1:F1000)=2008),--(ISNUMBER(H1:H1000)),--(H1:H1000>=0))
 
As a test I did a small range and it worked fine:

=AVERAGE(IF((YEAR(F2739:F2741)=2008)*(ISNUMBER(H2739: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(H2738:H2741))*(H2738:H2741>=0),H2738:H2741))

Formatting was checked and both were arrays.
 
As a test I did a small range and it worked fine:

=AVERAGE(IF((YEAR(F2739:F2741)=2008)*(ISNUMBER(H2739: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(H2738:H2741))*(H2738:H2741>=0),H2738:H2741))

Formatting was checked and both were arrays.
 
(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!
 
(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!
 
Back
Top