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

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