Average in Cells

[martins]

I know how to calculate the Average within a range of cells - however, what I would like to do is calculate the average but for only those cells that return a figure based on the results of another formula - example:if you have 6 cells but only 3 cells return a value, I would like the average calculated on the 3 cells and not based on 6 - and similarly, if another 2 figures are entered, the formula then calculates the average of the 5 figures - hope this makes sense.

[CLR]

You could use a combination formula to get your average..........

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

Adjust the ranges as needed........

Vaya con Dios,
Chuck, CABGx3



"martins" wrote:

>
> I know how to calculate the Average within a range of cells - however,
> what I would like to do is calculate the average but for only those
> cells that return a figure based on the results of another formula -
> example:if you have 6 cells but only 3 cells return a value, I would
> like the average calculated on the 3 cells and not based on 6 - and
> similarly, if another 2 figures are entered, the formula then
> calculates the average of the 5 figures - hope this makes sense.
>
>
> --
> martins
> ------------------------------------------------------------------------
> martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
> View this thread: http://www.excelforum.com/showthread...hreadid=526065
>
>

[martins]

Yep - does the trick - was also wondering how you would allow for a minus figure within the list of cells to add, as occasionally this does happen -

[CLR]

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

Vaya con Dios,
Chuck, CABGx3



"martins" wrote:

>
> Yep - does the trick - was also wondering how you would allow for a
> minus figure within the list of cells to add, as occasionally this does
> happen -
>
>
> --
> martins
> ------------------------------------------------------------------------
> martins's Profile: http://www.excelforum.com/member.php...o&userid=31616
> View this thread: http://www.excelforum.com/showthread...hreadid=526065
>
>

[Bernard Liengme]

Then again, SUMMING zero does noting, while COUNTING does so
=SUM(A1:A10)/COUNTIF(A1:A10,"<>0")
best wishes from a pedantic me!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"CLR" <[email protected]> wrote in message
news:[email protected]...
> =SUMIF(A1:A10,"<>0")/COUNTIF(A1:A10,"<>0")
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "martins" wrote:
>
>>
>> Yep - does the trick - was also wondering how you would allow for a
>> minus figure within the list of cells to add, as occasionally this does
>> happen -
>>
>>
>> --
>> martins
>> ------------------------------------------------------------------------
>> martins's Profile:
>> http://www.excelforum.com/member.php...o&userid=31616
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=526065
>>
>>

[martins]

To introduce another scenario - if I added another column which then gave a total based on different calulation, how would you configure the count formula as advised here - to count only the column which returned a result?