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.

 

[britwiz]

Hi martins

Try this:

=SUM(myRange)/COUNTIF(myRange,">0")

Regards

Steve

 

[britwiz]

Hi martins

Try this:


=SUM(myRange)/COUNTIF(myRange,"<>0")


Regards


Steve

 

[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]

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

 

[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!

 

[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?