software

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 quot;xquot;. if quot;xquot; is not
present in cell A13 then only calculate the average of A1,A11,A12 amp; omit
results of cell A14.

Maybe just brute force would work ok:

=SUM(A1,A11:A12,IF(A13=quot;xquot;,A14))
/(COUNT(A1,A11:A12) ((A13=quot;xquot;)*ISNUMBER(A14)))

(one cell)
Blackstar79 wrote:
gt;
gt; how do i calculate average of cells but only if certain cells say something.
gt; ex:
gt; average of cells A1,A11,A12,A14. average of those cells but for cell A14
gt; only include it in the average calculation if cell A13 has quot;xquot;. if quot;xquot; is not
gt; present in cell A13 then only calculate the average of A1,A11,A12 amp; omit
gt; results of cell A14.

--

Dave Peterson

Hi!

Try this:

=SUM(A1,A11:A12,IF(A13=quot;xquot;,A14,0))/(3 (A13=quot;xquot;))

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

=AVERAGE(A1,A11,A12,IF(A13=quot;xquot;,A14,quot;quot;))

Biff

quot;Blackstar79quot; gt; wrote in message
...
gt; how do i calculate average of cells but only if certain cells say
gt; something.
gt; ex:
gt; average of cells A1,A11,A12,A14. average of those cells but for cell A14
gt; only include it in the average calculation if cell A13 has quot;xquot;. if quot;xquot; is
gt; not
gt; present in cell A13 then only calculate the average of A1,A11,A12 amp; omit
gt; results of cell A14.
gt;Strange thing about AVERAGE. It's supposed to ignore TEXT but fails when
gt;you try something like this:
gt;=AVERAGE(A1,A11,A12,IF(A13=quot;xquot;,A14,quot;quot;))

Thanks to Harlan:

=AVERAGE(A1,A11,A12,IF(A13=quot;xquot;,A14,{quot;quot;}))

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUM(A1,A11:A12,IF(A13=quot;xquot;,A14,0))/(3 (A13=quot;xquot;))
gt;
gt; Strange thing about AVERAGE. It's supposed to ignore TEXT but fails when
gt; you try something like this:
gt;
gt; =AVERAGE(A1,A11,A12,IF(A13=quot;xquot;,A14,quot;quot;))
gt;
gt; Biff
gt;
gt; quot;Blackstar79quot; gt; wrote in message
gt; ...
gt;gt; how do i calculate average of cells but only if certain cells say
gt;gt; something.
gt;gt; ex:
gt;gt; average of cells A1,A11,A12,A14. average of those cells but for cell A14
gt;gt; only include it in the average calculation if cell A13 has quot;xquot;. if quot;xquot; is
gt;gt; not
gt;gt; present in cell A13 then only calculate the average of A1,A11,A12 amp; omit
gt;gt; results of cell A14.
gt;
gt;