Averageif function
 

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.

Averageif function
 

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

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

Averageif function
 

Excel 2002 doesn't support the AVERAGEIF function.

This array formula** will work in both versions:

=AVERAGE(IF(ISNUMBER(C9:I9),IF(C9:I91,C9:I9)))

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

--
Biff
Microsoft Excel MVP


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

Averageif function
 

You could use an array formula like: =AVERAGE(IF(C9:I91,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

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

Averageif function
 

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.