COUNT ONLY CELLS THAT AREN'T BLANK

[paulinec]

This is the present function I have to get an average of $D$4:$D$3710:
=(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
The problem with this is that it is counting all cells in $A$4:$A$3710 that
equal A3742.
To get a correct average I need to only count cells that are not blank in
$D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
Can anyone help me with this?
--
PaulineC

[[email protected]]

paulinec wrote...
>This is the present function I have to get an average of $D$4:$D$3710:
>=(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
>The problem with this is that it is counting all cells in $A$4:$A$3710
that
>equal A3742.
>To get a correct average I need to only count cells that are not blank
in
>$D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
....

You may be better off using the array formula

=AVERAGE(IF($A$4:$A$3710=A3742,$D$4:$D$3710))

which will skip nonnumeric cells in D4:D3710. If you can't use array
formulas, then you need to use a 2-criteria denominator.

=SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710)
/SUMPRODUCT(--($A$4:$A$3710=A3742),--ISNUMBER($D$4:$D$3710))

[Dave R.]

change that /(countif part to:

SUMPRODUCT(($A$4:$A$3710=A3742)*($D$4:$D$3710<>""))


"paulinec" <[email protected]> wrote in message
news:[email protected]...
> This is the present function I have to get an average of $D$4:$D$3710:
> =(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
> The problem with this is that it is counting all cells in $A$4:$A$3710
that
> equal A3742.
> To get a correct average I need to only count cells that are not blank in
> $D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
> Can anyone help me with this?
> --
> PaulineC

[William]

Hi Pauline

I'm not sure I completely understand your question, but to return an average
of cells D4:D3710 that are not blank use this array formula which you should
enter by pressing Ctrl|Shift|Enter at the same time rather than just "Enter"

{=AVERAGE(IF(D4:D3710<>"",D4:D3710))}

I dont see the relevance of cell A3742 - if you want an average of those
cells that equal cell A3742, then cell A3742 would be the average - what am
I missing?

--
XL2002
Regards

William

[email protected]

"paulinec" <[email protected]> wrote in message
news:[email protected]...
| This is the present function I have to get an average of $D$4:$D$3710:
| =(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
| The problem with this is that it is counting all cells in $A$4:$A$3710
that
| equal A3742.
| To get a correct average I need to only count cells that are not blank in
| $D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
| Can anyone help me with this?
| --
| PaulineC

[William]

Bad day - ignore the last part of my post :-(

--
XL2002
Regards

William

[email protected]

"William" <[email protected]> wrote in message
news:[email protected]...
| Hi Pauline
|
| I'm not sure I completely understand your question, but to return an
average
| of cells D4:D3710 that are not blank use this array formula which you
should
| enter by pressing Ctrl|Shift|Enter at the same time rather than just
"Enter"
|
| {=AVERAGE(IF(D4:D3710<>"",D4:D3710))}
|
| I dont see the relevance of cell A3742 - if you want an average of those
| cells that equal cell A3742, then cell A3742 would be the average - what
am
| I missing?
|
| --
| XL2002
| Regards
|
| William
|
| [email protected]
|
| "paulinec" <[email protected]> wrote in message
| news:[email protected]...
| | This is the present function I have to get an average of $D$4:$D$3710:
| | =(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
| | The problem with this is that it is counting all cells in $A$4:$A$3710
| that
| | equal A3742.
| | To get a correct average I need to only count cells that are not blank
in
| | $D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
| | Can anyone help me with this?
| | --
| | PaulineC
|
|
|

[paulinec]

To explain a bit further Row A1:3710 contains a farm number, Row B1:B3710
contains paddock nos, Row D1:D3710 contains a row length for each paddock. As
some paddocks are not planted there are some blank cells in the row length
column.

In row 4742 I am trying to get an average row lengh for each farm. To be
accurate it has to be an average of the cells that contain row lengths.

At present it is averaging on the total number of paddocks in each farm.

"William" wrote:

> Bad day - ignore the last part of my post :-(
>
> --
> XL2002
> Regards
>
> William
>
> [email protected]
>
> "William" <[email protected]> wrote in message
> news:[email protected]...
> | Hi Pauline
> |
> | I'm not sure I completely understand your question, but to return an
> average
> | of cells D4:D3710 that are not blank use this array formula which you
> should
> | enter by pressing Ctrl|Shift|Enter at the same time rather than just
> "Enter"
> |
> | {=AVERAGE(IF(D4:D3710<>"",D4:D3710))}
> |
> | I dont see the relevance of cell A3742 - if you want an average of those
> | cells that equal cell A3742, then cell A3742 would be the average - what
> am
> | I missing?
> |
> | --
> | XL2002
> | Regards
> |
> | William
> |
> | [email protected]
> |
> | "paulinec" <[email protected]> wrote in message
> | news:[email protected]...
> | | This is the present function I have to get an average of $D$4:$D$3710:
> | | =(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
> | | The problem with this is that it is counting all cells in $A$4:$A$3710
> | that
> | | equal A3742.
> | | To get a correct average I need to only count cells that are not blank
> in
> | | $D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
> | | Can anyone help me with this?
> | | --
> | | PaulineC
> |
> |
> |
>
>
>

[William]

Hi Pauline
Try...
=AVERAGE(IF(D4:D3710<>"",IF(A4:A3710=F4742,D4:D3710)))
where the farm number you are trying to average is in cell F4742

--
XL2002
Regards

William

[email protected]

"paulinec" <[email protected]> wrote in message
news:[email protected]...
| To explain a bit further Row A1:3710 contains a farm number, Row B1:B3710
| contains paddock nos, Row D1:D3710 contains a row length for each paddock.
As
| some paddocks are not planted there are some blank cells in the row length
| column.
|
| In row 4742 I am trying to get an average row lengh for each farm. To be
| accurate it has to be an average of the cells that contain row lengths.
|
| At present it is averaging on the total number of paddocks in each farm.
|
| "William" wrote:
|
| > Bad day - ignore the last part of my post :-(
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > [email protected]
| >
| > "William" <[email protected]> wrote in message
| > news:[email protected]...
| > | Hi Pauline
| > |
| > | I'm not sure I completely understand your question, but to return an
| > average
| > | of cells D4:D3710 that are not blank use this array formula which you
| > should
| > | enter by pressing Ctrl|Shift|Enter at the same time rather than just
| > "Enter"
| > |
| > | {=AVERAGE(IF(D4:D3710<>"",D4:D3710))}
| > |
| > | I dont see the relevance of cell A3742 - if you want an average of
those
| > | cells that equal cell A3742, then cell A3742 would be the average -
what
| > am
| > | I missing?
| > |
| > | --
| > | XL2002
| > | Regards
| > |
| > | William
| > |
| > | [email protected]
| > |
| > | "paulinec" <[email protected]> wrote in message
| > | news:[email protected]...
| > | | This is the present function I have to get an average of
$D$4:$D$3710:
| > | |
=(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
| > | | The problem with this is that it is counting all cells in
$A$4:$A$3710
| > | that
| > | | equal A3742.
| > | | To get a correct average I need to only count cells that are not
blank
| > in
| > | | $D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
| > | | Can anyone help me with this?
| > | | --
| > | | PaulineC
| > |
| > |
| > |
| >
| >
| >

[paulinec]

Yes, that worked, thank you so much for that. Just one more question. What
is the reason for the two -- in the formula?

"[email protected]" wrote:

> paulinec wrote...
> >This is the present function I have to get an average of $D$4:$D$3710:
> >=(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
> >The problem with this is that it is counting all cells in $A$4:$A$3710
> that
> >equal A3742.
> >To get a correct average I need to only count cells that are not blank
> in
> >$D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
> ....
>
> You may be better off using the array formula
>
> =AVERAGE(IF($A$4:$A$3710=A3742,$D$4:$D$3710))
>
> which will skip nonnumeric cells in D4:D3710. If you can't use array
> formulas, then you need to use a 2-criteria denominator.
>
> =SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710)
> /SUMPRODUCT(--($A$4:$A$3710=A3742),--ISNUMBER($D$4:$D$3710))
>
>

[tjtjjtjt]

It's used to convert the True and False to 1 and 0.
The first - converts them to -1 and 0.
The second - gets them to 1 and 0.
These are then used in the SUMPRODUCT Function.
For more on SUMPRODUCT in general:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

tj

"paulinec" wrote:

> Yes, that worked, thank you so much for that. Just one more question. What
> is the reason for the two -- in the formula?
>
> "[email protected]" wrote:
>
> > paulinec wrote...
> > >This is the present function I have to get an average of $D$4:$D$3710:
> > >=(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
> > >The problem with this is that it is counting all cells in $A$4:$A$3710
> > that
> > >equal A3742.
> > >To get a correct average I need to only count cells that are not blank
> > in
> > >$D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
> > ....
> >
> > You may be better off using the array formula
> >
> > =AVERAGE(IF($A$4:$A$3710=A3742,$D$4:$D$3710))
> >
> > which will skip nonnumeric cells in D4:D3710. If you can't use array
> > formulas, then you need to use a 2-criteria denominator.
> >
> > =SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710)
> > /SUMPRODUCT(--($A$4:$A$3710=A3742),--ISNUMBER($D$4:$D$3710))
> >
> >