omiting cells in average calcs
 

how do i calculate average of cells but only if certain cells say something.
ex:
average of cells A1,A11,A12,A14. average of those cells but for cell A14
only include it in the average calculation if cell A13 has "x". if "x" is not
present in cell A13 then only calculate the average of A1,A11,A12 & omit
results of cell A14.

omiting cells in average calcs
 

Maybe just brute force would work ok:

=SUM(A1,A11:A12,IF(A13="x",A14))
/(COUNT(A1,A11:A12)+((A13="x")*ISNUMBER(A14)))

(one cell)



Blackstar79 wrote:

how do i calculate average of cells but only if certain cells say something.
ex:
average of cells A1,A11,A12,A14. average of those cells but for cell A14
only include it in the average calculation if cell A13 has "x". if "x" is not
present in cell A13 then only calculate the average of A1,A11,A12 & omit
results of cell A14.

--

Dave Peterson

omiting cells in average calcs
 

Hi!

Try this:

=SUM(A1,A11:A12,IF(A13="x",A14,0))/(3+(A13="x"))

Strange thing about AVERAGE. It's supposed to ignore TEXT but fails when you
try something like this:

=AVERAGE(A1,A11,A12,IF(A13="x",A14,""))

Biff

"Blackstar79" wrote in message
...
how do i calculate average of cells but only if certain cells say
something.
ex:
average of cells A1,A11,A12,A14. average of those cells but for cell A14
only include it in the average calculation if cell A13 has "x". if "x" is
not
present in cell A13 then only calculate the average of A1,A11,A12 & omit
results of cell A14.

omiting cells in average calcs
 

Strange thing about AVERAGE. It's supposed to ignore TEXT but fails when
you try something like this:
=AVERAGE(A1,A11,A12,IF(A13="x",A14,""))

Thanks to Harlan:

=AVERAGE(A1,A11,A12,IF(A13="x",A14,{""}))

Biff

"Biff" wrote in message
...
Hi!

Try this:

=SUM(A1,A11:A12,IF(A13="x",A14,0))/(3+(A13="x"))

Strange thing about AVERAGE. It's supposed to ignore TEXT but fails when
you try something like this:

=AVERAGE(A1,A11,A12,IF(A13="x",A14,""))

Biff

"Blackstar79" wrote in message
...
how do i calculate average of cells but only if certain cells say
something.
ex:
average of cells A1,A11,A12,A14. average of those cells but for cell A14
only include it in the average calculation if cell A13 has "x". if "x" is
not
present in cell A13 then only calculate the average of A1,A11,A12 & omit
results of cell A14.