=IF(F4:F7=quot;quot;,quot;quot;,AVERAGE(IF(F4:F7lt;gt;0,F4:F7,quot;quot;)))
i am using this equation w the ctrl shift enter and thats fine, however i
only get the average if all cells have a value, which is not what i want. i
want the average of all cells w a value. i am not sure how to write if any of
F4 to F7 is empty, as F4:F7 seems to mean that they all have to have a value.

thanks


=AVERAGE(F4:F7)

would suffice.

Micayla Bergen wrote:
gt; =IF(F4:F7=quot;quot;,quot;quot;,AVERAGE(IF(F4:F7lt;gt;0,F4:F7,quot;quot;)))
gt; i am using this equation w the ctrl shift enter and thats fine, however i
gt; only get the average if all cells have a value, which is not what i want. i
gt; want the average of all cells w a value. i am not sure how to write if any of
gt; F4 to F7 is empty, as F4:F7 seems to mean that they all have to have a value.
gt;
gt; thanks

Hi!

The problem is with this portion of the formula:

=IF(F4:F7=quot;quot;,quot;quot;,

Maybe try: (array entered)

=IF(COUNTIF(F4:F7,quot;lt;gt;0quot;),AVERAGE(IF(F4:F7lt;gt;0,F4:F7 )),quot;quot;)

Biff

quot;Micayla Bergenquot; gt; wrote in message
news
gt; =IF(F4:F7=quot;quot;,quot;quot;,AVERAGE(IF(F4:F7lt;gt;0,F4:F7,quot;quot;)))
gt; i am using this equation w the ctrl shift enter and thats fine, however i
gt; only get the average if all cells have a value, which is not what i want.
gt; i
gt; want the average of all cells w a value. i am not sure how to write if any
gt; of
gt; F4 to F7 is empty, as F4:F7 seems to mean that they all have to have a
gt; value.
gt;
gt; thanks
but i want to avoid the #DIV/0!
my 3 cells are months, so i want the average of the first 2 before the end
of the third.

quot;Aladin Akyurekquot; wrote:

gt;
gt; =AVERAGE(F4:F7)
gt;
gt; would suffice.
gt;
gt; Micayla Bergen wrote:
gt; gt; =IF(F4:F7=quot;quot;,quot;quot;,AVERAGE(IF(F4:F7lt;gt;0,F4:F7,quot;quot;)))
gt; gt; i am using this equation w the ctrl shift enter and thats fine, however i
gt; gt; only get the average if all cells have a value, which is not what i want. i
gt; gt; want the average of all cells w a value. i am not sure how to write if any of
gt; gt; F4 to F7 is empty, as F4:F7 seems to mean that they all have to have a value.
gt; gt;
gt; gt; thanks
gt;

Disregard that reply!

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; The problem is with this portion of the formula:
gt;
gt; =IF(F4:F7=quot;quot;,quot;quot;,
gt;
gt; Maybe try: (array entered)
gt;
gt; =IF(COUNTIF(F4:F7,quot;lt;gt;0quot;),AVERAGE(IF(F4:F7lt;gt;0,F4:F7 )),quot;quot;)
gt;
gt; Biff
gt;
gt; quot;Micayla Bergenquot; gt; wrote in
gt; message news
gt;gt; =IF(F4:F7=quot;quot;,quot;quot;,AVERAGE(IF(F4:F7lt;gt;0,F4:F7,quot;quot;)))
gt;gt; i am using this equation w the ctrl shift enter and thats fine, however i
gt;gt; only get the average if all cells have a value, which is not what i want.
gt;gt; i
gt;gt; want the average of all cells w a value. i am not sure how to write if
gt;gt; any of
gt;gt; F4 to F7 is empty, as F4:F7 seems to mean that they all have to have a
gt;gt; value.
gt;gt;
gt;gt; thanks
gt;
gt;
gt; but i want to avoid the #DIV/0!

Do you have blank cells or do you have zeros in some cells that you want to
exclude?

If it's blank cells causing the #DIV/0!, try:

=IF(COUNT(F4:F7),AVERAGE(F4:F7),quot;quot;)

Normally entered, not an array.

Biff

quot;Micayla Bergenquot; gt; wrote in message
news
gt; but i want to avoid the #DIV/0!
gt; my 3 cells are months, so i want the average of the first 2 before the end
gt; of the third.
gt;
gt; quot;Aladin Akyurekquot; wrote:
gt;
gt;gt;
gt;gt; =AVERAGE(F4:F7)
gt;gt;
gt;gt; would suffice.
gt;gt;
gt;gt; Micayla Bergen wrote:
gt;gt; gt; =IF(F4:F7=quot;quot;,quot;quot;,AVERAGE(IF(F4:F7lt;gt;0,F4:F7,quot;quot;)))
gt;gt; gt; i am using this equation w the ctrl shift enter and thats fine, however
gt;gt; gt; i
gt;gt; gt; only get the average if all cells have a value, which is not what i
gt;gt; gt; want. i
gt;gt; gt; want the average of all cells w a value. i am not sure how to write if
gt;gt; gt; any of
gt;gt; gt; F4 to F7 is empty, as F4:F7 seems to mean that they all have to have a
gt;gt; gt; value.
gt;gt; gt;
gt;gt; gt; thanks
gt;gt;
Hi,

Try the following array formula

=average(if(not(iserror(range)),range))

Regards,

quot;Micayla Bergenquot; wrote:

gt; =IF(F4:F7=quot;quot;,quot;quot;,AVERAGE(IF(F4:F7lt;gt;0,F4:F7,quot;quot;)))
gt; i am using this equation w the ctrl shift enter and thats fine, however i
gt; only get the average if all cells have a value, which is not what i want. i
gt; want the average of all cells w a value. i am not sure how to write if any of
gt; F4 to F7 is empty, as F4:F7 seems to mean that they all have to have a value.
gt;
gt; thanks

One way (array-entered):

=IF(ISERR(AVERAGE(IF(F4:F7lt;gt;0,F4:F7,FALSE))),quot;quot;,
AVERAGE(IF(F4:F7lt;gt;0,F4:F7,FALSE)))

However, I'm confused - F4:F7 is *4* cells, not 3...In article gt;,
quot;Micayla Bergenquot; gt; wrote:

gt; but i want to avoid the #DIV/0!
gt; my 3 cells are months, so i want the average of the first 2 before the end
gt; of the third.