Excel formula too long

[nat.ssnt]

Hi All,

I am trying to incorporate =IF(ISERROR(OriginalFormula),"",OriginalFormula) to the formula below, as it can generate #DIV/0! error if there is not data, but excel won't allow as formula is too long.
Any suggestion how to?

I am not familiar with macro, can this be done through excel formula? otherwise if not, any advice on how to do the macro?

=AVERAGE(IF(((Database!$C$2:$C$14>=$B$4)+($B$4=""))*((Database!$C$2:$C$14<='Output - Summary'!$C$4)+($B$5=""))*((Database!$B$2:$B$14='Output - Summary'!$B$5)+($B$5=""))*(Database!$Z$2:$Z$14=$A$24)*((Database!$X$2:$X$14='Output - Summary'!$B$9)+('Output - Summary'!$B$9=""))*((Database!$E$2:$E$14='Output - Summary'!$B$10)+('Output - Summary'!$B$10=""))*((Database!$G$2:$G$14>=$B$11)+($B$11=""))*((Database!$G$2:$G$14<=$C$11)+($C$11=""))*((Database!$H$2:$H$14='Output - Summary'!$B$12)+('Output - Summary'!$B$12=""))*((Database!$J$2:$J$14>='Output - Summary'!$B$13)+('Output - Summary'!$B$13=""))*((Database!$J$2:$J$14<='Output - Summary'!$C$13)+('Output - Summary'!$C$13=""))*((Database!$L$2:$L$14='Output - Summary'!$B$14)+('Output - Summary'!$B$14=""))*((Database!$M$2:$M$14>=$B$15)+($B$15=""))*((Database!$M$2:$M$14<='Output - Summary'!$C$15)+('Output - Summary'!$C$15="")),Database!$P$2:$P$14,""))

Thanks & look forward to your advice!

Rgds,
Nat

[JBeaucaire]

Suggestion 1: Switch to an later version of Excel. Even Excel 2007 has the IFERROR() function and can be gotten for a steal nowadays.


Suggestion 2: Add IFERROR() function to your workbook.

Here's the macro code you can install:

Please Login or Register  to view this content.

IFERROR explanation http://www.excelforum.com/excel-new-...matting.html#3



How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use.

=IFERROR(Original Formula, "")

[nat.ssnt]

Thanks Jerry, Works like charm!

I've got a similar issue, but this formular gives me '0' instead of any error. Is there a way to reflect the '0' as "blank" instead? Iferror does not work in this case.

=MAX(IF(((Database!$C$2:$C$14>=$B$3)+($B$3=""))*((Database!$C$2:$C$14<=$C$3)+($B$4=""))*((Database!$B$2:$B$14=$B$4)+($B$4=""))*(Database!$Z$2:$Z$14=$A$29)*((Database!$X$2:$X$14=$B$8)+($B$8=""))*((Database!$E$2:$E$14=$B$9)+($B$9=""))*((Database!$G$2:$G$14>=$B$10)+($B$10=""))*((Database!$G$2:$G$14<=$C$10)+($C$10=""))*((Database!$H$2:$H$14=$B$11)+($B$11=""))*((Database!$J$2:$J$14>=$B$12)+($B$12=""))*((Database!$J$2:$J$14<=$C$12)+($C$12=""))*((Database!$L$2:$L$14=$B$13)+($B$13=""))*((Database!$M$2:$M$14>=$B$14)+($B$14="")),Database!$P$2:$P$14,""))

Thanks so much!

Rgds,
Nat

[JBeaucaire]

Lots of ways to suppress 0 from displaying in a cell, I use none of them, zero is a legitimate result.

All the best suggestions can be found here: http://office.microsoft.com/en-us/ex...005199879.aspx



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[nat.ssnt]

Thanks Jerry! All good now