| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Iserror function in combination with average
Hi,
The formula below looks for personnel in a certain group (column J) and within that group a certain age (column O) and then gives me the average salary (from column P). Where there are no persons with a certain age I get #DIV/0 as result. I want to use the ISERROR function to not display the #DIV/0. Is it possible?, if yes, where should I enter it. It constantly gives me that my formula contains an error. Thanks & Regards, Lupe =AVERAGE(IF((ISNUMBER('[All Personnel.xls]downloaded'!$P$2:$P$991))*(('[All Personnel.xls]downloaded'!$J$2:$J$991))=$B15,(IF((ISNUMBER('[All Personnel.xls]downloaded'!$P$2:$P$991))*(('[All Personnel.xls]downloaded'!$O$2:$O$991))=D$2,'[All Personnel.xls]downloaded'!$P$2:$P$991)))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Iserror function in combination with average
Hi,
I kept looking and found a solution on http://www.excel-vba.com/excel-10-iserror.htm. It helped, hope this was the best solution as now my formula is as big as ever. =IF(ISERROR(AVERAGE(IF((ISNUMBER('[All Personnel.xls]downloaded'!$P$2:$P$991))*(('[All Personnel.xls]downloaded'!$J$2:$J$991))=$B15,(IF((ISNUMBER('[All Personnel.xls]downloaded'!$P$2:$P$991))*(('[All Personnel.xls]downloaded'!$O$2:$O$991))=D$2,'[All Personnel.xls]downloaded'!$P$2:$P$991)))))," ",AVERAGE(IF((ISNUMBER('[All Personnel.xls]downloaded'!$P$2:$P$991))*(('[All Personnel.xls]downloaded'!$J$2:$J$991))=$B5,(IF((ISNUMBER('[All Personnel.xls]downloaded'!$P$2:$P$991))*(('[All Personnel.xls]downloaded'!$O$2:$O$991))=D$2,'[All Personnel.xls]downloaded'!$P$2:$P$991))))) "Lupe" wrote: Hi, The formula below looks for personnel in a certain group (column J) and within that group a certain age (column O) and then gives me the average salary (from column P). Where there are no persons with a certain age I get #DIV/0 as result. I want to use the ISERROR function to not display the #DIV/0. Is it possible?, if yes, where should I enter it. It constantly gives me that my formula contains an error. Thanks & Regards, Lupe =AVERAGE(IF((ISNUMBER('[All Personnel.xls]downloaded'!$P$2:$P$991))*(('[All Personnel.xls]downloaded'!$J$2:$J$991))=$B15,(IF((ISNUMBER('[All Personnel.xls]downloaded'!$P$2:$P$991))*(('[All Personnel.xls]downloaded'!$O$2:$O$991))=D$2,'[All Personnel.xls]downloaded'!$P$2:$P$991)))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with Combination function | Excel Worksheet Functions | |||
| Excel ISERROR function | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| How do I throw in an ISERROR function on a complicated VVLOOKUP? | Excel Discussion (Misc queries) | |||
| how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
Linear Mode
