Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there any way to get an AVERAGEIF & MEDIANIF function in Exce | Excel Worksheet Functions | |||
averageif function | Excel Worksheet Functions | |||
EXCEL -- want to do have ''averageif'' - like sumif function - H. | Excel Worksheet Functions | |||
Create an AVERAGEIF function. | Excel Worksheet Functions | |||
AVERAGEIF Function out there? | Excel Discussion (Misc queries) |