Averageif function

[Kevin Anderson]

I wrote a spreadsheet in Excel 2007 that uses the =AVERAGEIF function, when I
loaded it on to my work computer that has Excel 2002, it does not work. It
there a way to work around this. The formula that I wrote is
=AVERAGEIF(C9:I9,">1"). It is a sales averaging formula, since the formula
refers to seven days and very few people work seven days there will be some
error references (#N/A) in the fields that I want to average. Any help would
be appreciated.

 

[Fred Smith]

Why not fix the N/As? Use something like:

=if(isna(yourformula),"",yourformula)

It will make your spreadsheet cleaner, and Average will ignore the blanks.

Regards,
Fred

 

[T. Valko]

Excel 2002 doesn't support the AVERAGEIF function.

This array formula** will work in both versions:

=AVERAGE(IF(ISNUMBER(C9:I9),IF(C9:I9>1,C9:I9)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Tyro]

You could use an array formula like: =AVERAGE(IF(C9:I9>1,C9:I9)). After you
type the formula in, you press Ctrl+Shift+Enter not just Enter. You'll see
that Excel places {} around the formula in the formula bar to indicate an
array formula. You never enter the {}. As far as the #N/A is concerned you
didn't state what you want to do in that event.

Tyro

 

[Jim Cone]

If a non array formula is preferred then...
=SUMIF(C9:I9,">1",C9:I9)/COUNTIF(C9:I9,">1")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Kevin Anderson"
wrote in message
I wrote a spreadsheet in Excel 2007 that uses the =AVERAGEIF function, when I
loaded it on to my work computer that has Excel 2002, it does not work. It
there a way to work around this. The formula that I wrote is
=AVERAGEIF(C9:I9,">1"). It is a sales averaging formula, since the formula
refers to seven days and very few people work seven days there will be some
error references (#N/A) in the fields that I want to average. Any help would
be appreciated.