if(iserror

[bluepenink]

Hello

i am using excel 03.

here is my formula

{=AVERAGE(IF(FREQUENCY(IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)>0,FREQUENCY(IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)))}

it is currently giving me a div/0 error, which is fine

but when i try to add the if(iserror(....it says to many arguments

can someone help? how can i make the div/0 appear as 0?

 

[Andrew Poulsom]

Adding ISERROR would result in too many nested functions. One solution would be to put that formula in some other cell and reference that cell with ISERROR for your output.

 

[Excelestial]

I would imagine Excel 2003 will limit your use of nested IF's. Excel 2007 yielded no errors with the following:

=IF(ISERROR(AVERAGE(
IF(FREQUENCY(
IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),
IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)>0,FREQUENCY(IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)))),"x",(AVERAGE(
IF(FREQUENCY(
IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),
IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)>0,FREQUENCY(IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)))))

 

[T. Valko]

I would imagine Excel 2003 will limit your use of nested IF's. Excel 2007 yielded no errors with the following:

=IF(ISERROR(AVERAGE(
IF(FREQUENCY(
IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),
IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)>0,FREQUENCY(IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)))),"x",(AVERAGE(
IF(FREQUENCY(
IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),
IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)>0,FREQUENCY(IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,21)=K69)),IF(INDEX(_Data,0,12)<>"",MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)))))

If using Excel 2007 or later then you can use the IFERROR function.

=IFERROR(your_formula_here,___)

Where ___ = the result you want to replace any error.

Note that IFERROR will trap all errors, not just #DIV/0! errors.