I need a formula to average cells only if there is a number in a cell.
Example in cell A1, A2,A3 A4 they all have a formulasand all 4 cells say 0.00
in cell A5 i have =average(A1:A4)

So if I put 100 in cell A1 then my answer in cell A5 says 25

I Would like it to say 100
only average if the number is higher that 0.00

Thanks

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

or


=AVERAGE(IF(A1:A4>0,A1:A4))


the latter entered with ctrl + shift & enter


You might want to test for 0 in the cells because if all cells are zero it
will return a div error


=IF(COUNTIF(A1:A4,0)=4,0,rest of the formula





--


Regards,


Peo Sjoblom


"delete automatically" <(E-Mail Removed)> wrote
in message news:4D6F70FC-4FB9-4B6F-B036-(E-Mail Removed)...
>I need a formula to average cells only if there is a number in a cell.
> Example in cell A1, A2,A3 A4 they all have a formulasand all 4 cells say
> 0.00
> in cell A5 i have =average(A1:A4)
>
> So if I put 100 in cell A1 then my answer in cell A5 says 25
>
> I Would like it to say 100
> only average if the number is higher that 0.00
>
> Thanks

At one stage you say average only if there is a number in the cell, but
later you say only if the number is greater than zero.

The AVERAGE function will only take account of cells with numbers in, and
will ignore empty cells or those with text.
If you want to ignore cells where the number is <=0, then try
=AVERAGE(IF(A1:A4>0,A1:A4,"")) as an array formula (Control Shift Enter).
--
David Biddulph

"delete automatically" <(E-Mail Removed)> wrote
in message news:4D6F70FC-4FB9-4B6F-B036-(E-Mail Removed)...
>I need a formula to average cells only if there is a number in a cell.
> Example in cell A1, A2,A3 A4 they all have a formulasand all 4 cells say
> 0.00
> in cell A5 i have =average(A1:A4)
>
> So if I put 100 in cell A1 then my answer in cell A5 says 25
>
> I Would like it to say 100
> only average if the number is higher that 0.00
>
> Thanks

ok heres what I have.
in cell D6 I have formula of =AVERAGE(D710) which its at #div/0!
in cell D11 = AVERAGE(D1215) which its at 85
in cell D16 =AVERAGE(D17210) which its at 50

in cell D3 I just want the average of cell D6,D11,and D16
is this possible and how?



"Peo Sjoblom" wrote:

> =SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")
>
> or
>
>
> =AVERAGE(IF(A1:A4>0,A1:A4))
>
>
> the latter entered with ctrl + shift & enter
>
>
> You might want to test for 0 in the cells because if all cells are zero it
> will return a div error
>
>
> =IF(COUNTIF(A1:A4,0)=4,0,rest of the formula
>
>
>
>
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
>
> "delete automatically" <(E-Mail Removed)> wrote
> in message news:4D6F70FC-4FB9-4B6F-B036-(E-Mail Removed)...
> >I need a formula to average cells only if there is a number in a cell.
> > Example in cell A1, A2,A3 A4 they all have a formulasand all 4 cells say
> > 0.00
> > in cell A5 i have =average(A1:A4)
> >
> > So if I put 100 in cell A1 then my answer in cell A5 says 25
> >
> > I Would like it to say 100
> > only average if the number is higher that 0.00
> >
> > Thanks
>
>
>

So what was wrong with Peo's suggestion?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"delete automatically" <(E-Mail Removed)> wrote
in message news:44F9955B-F21F-482B-8A7F-(E-Mail Removed)...
>
> ok heres what I have.
> in cell D6 I have formula of =AVERAGE(D710) which its at #div/0!
> in cell D11 = AVERAGE(D1215) which its at 85
> in cell D16 =AVERAGE(D17210) which its at 50
>
> in cell D3 I just want the average of cell D6,D11,and D16
> is this possible and how?
>
>
>
> "Peo Sjoblom" wrote:
>
>> =SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")
>>
>> or
>>
>>
>> =AVERAGE(IF(A1:A4>0,A1:A4))
>>
>>
>> the latter entered with ctrl + shift & enter
>>
>>
>> You might want to test for 0 in the cells because if all cells are zero
>> it
>> will return a div error
>>
>>
>> =IF(COUNTIF(A1:A4,0)=4,0,rest of the formula
>>
>>
>>
>>
>>
>> --
>>
>>
>> Regards,
>>
>>
>> Peo Sjoblom
>>
>>
>> "delete automatically" <(E-Mail Removed)>
>> wrote
>> in message news:4D6F70FC-4FB9-4B6F-B036-(E-Mail Removed)...
>> >I need a formula to average cells only if there is a number in a cell.
>> > Example in cell A1, A2,A3 A4 they all have a formulasand all 4 cells
>> > say
>> > 0.00
>> > in cell A5 i have =average(A1:A4)
>> >
>> > So if I put 100 in cell A1 then my answer in cell A5 says 25
>> >
>> > I Would like it to say 100
>> > only average if the number is higher that 0.00
>> >
>> > Thanks
>>
>>
>>