average w IF

M

Micayla Bergen

=IF(F4:F7="","",AVERAGE(IF(F4:F7<>0,F4:F7,"")))
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
 
B

Biff

Hi!

The problem is with this portion of the formula:

=IF(F4:F7="","",

Maybe try: (array entered)

=IF(COUNTIF(F4:F7,"<>0"),AVERAGE(IF(F4:F7<>0,F4:F7)),"")

Biff
 
M

Micayla Bergen

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.
 
B

Biff

Disregard that reply!

Biff

Biff said:
Hi!

The problem is with this portion of the formula:

=IF(F4:F7="","",

Maybe try: (array entered)

=IF(COUNTIF(F4:F7,"<>0"),AVERAGE(IF(F4:F7<>0,F4:F7)),"")

Biff
Click to expand...
 
B

Biff

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),"")

Normally entered, not an array.

Biff
 
A

Ashish Mathur

Hi,

Try the following array formula

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

Regards,
 
J

JE McGimpsey

One way (array-entered):

=IF(ISERR(AVERAGE(IF(F4:F7<>0,F4:F7,FALSE))),"",
AVERAGE(IF(F4:F7<>0,F4:F7,FALSE)))

However, I'm confused - F4:F7 is *4* cells, not 3...
 
Top