Adding "ISERROR" to Formula?

[~EVH~]

I'm trying to avoid the "#N/A" result from each of the below formulas... I've tried to add the "ISERROR" function to each of the below, but must be screwing something up.

Can I trouble you folks for the proper way of adding the "ISERROR" function for each of the below formulas?

Formula #1:
=IF(SUM(E3:AC3)=0,"0",LOOKUP(9.99999999999999E+307,E3:AC3))

Formula #2:
=LOOKUP(9.99999999999999E+307,E3:INDEX(E3:AC3,MATCH(9.99999999999999E+307,E3:AC3)-1))


Thanks in advance!

 

[Aladin Akyurek]

I'm trying to avoid the "#N/A" result from each of the below formulas... I've tried to add the "ISERROR" function to each of the below, but must be screwing something up.

Can I trouble you folks for the proper way of adding the "ISERROR" function for each of the below formulas?

Formula #1:
=IF(SUM(E3:AC3)=0,"0",LOOKUP(9.99999999999999E+307,E3:AC3))

Formula #2:
=LOOKUP(9.99999999999999E+307,E3:INDEX(E3:AC3,MATCH(9.99999999999999E+307,E3:AC3)-1))


Thanks in advance!

Define BigNum via Insert|Name|Define as referring to:

=9.99999999999999E+307

If really necessary to avoid #N/A...

=IF(COUNT(E3:AC3),LOOKUP(BigNum,E3:AC3),0)

=IF(COUNT(E3:AC3)>1,LOOKUP(BigNum,E3:INDEX(E3:AC3,MATCH(BigNum,E3:AC3)-1)),0)

 

[~EVH~]

Define BigNum via Insert|Name|Define as referring to:

=9.99999999999999E+307

If really necessary to avoid #N/A...

=IF(COUNT(E3:AC3),LOOKUP(BigNum,E3:AC3),0)

=IF(COUNT(E3:AC3)>1,LOOKUP(BigNum,E3:INDEX(E3:AC3,MATCH(BigNum,E3:AC3)-1)),0)

Thank you, Aladin!!!

I'm curious... in the first formula, how come you don't have to define a variable (e.g., >, <, =, etc.) as you did in the second formula, such as:

=IF(COUNT(E3:AC3)>???,LOOKUP(BigNum,E3:AC3),0)

 

[Aladin Akyurek]

Thank you, Aladin!!!

You are welcome.

I'm curious... in the first formula, how come you don't have to define a variable (e.g., >, <, =, etc.) as you did in the second formula, such as:

=IF(COUNT(E3:AC3)>???,LOOKUP(BigNum,E3:AC3),0)

IF(COUNT(E3:AC3)

is the same as

IF(COUNT(E3:AC3) > 0

A non-zero count means a TRUE result, which leads to the evaluation of the LOOKUP bit, a 0 count leads to 0 as result. That's the reason why the > 0 test is often omitted.

 

[~EVH~]

Ah... thank you again!