Need Help With if(iserror

[isadoko]

I have tried to modify this formula to show "Open" instead of #Num!. I am unable to write a working version. Thanks in advance for your help.

=IF(AM2<Factors!$H$2,"< 48H", IF(AM2=Factors!$H$2,"= 48H",IF(AM2>Factors!$H$2,"> 48H*","")))

 

[FKoenig]

Try somehting like

=IF(ISERROR(IF(AM2Factors!$H$2,"> 48H*","")),"Open",IF(AM2Factors!$H$2,"> 48H*",""))

 

[isadoko]

Thanks FKoenig

I realize my entire formula did not display in posting... I hope it now shows all factors.

=IF(AM2<Factors!$H$2,"< 48H", IF(AM2=Factors!$H$2,"= 48H",IF(AM2>Factors!$H$2,"> 48H*","Open")))

 

[Aladin Akyurek]

Thanks FKoenig

I realize my entire formula did not display in posting... I hope it now shows all factors.

=IF(AM2<FACTORS!$H$2,"< 48H?,IF(AM2 IF(AM2='Factors!$H$2,"=' 48H?,>Factors!$H$2,"> 48H*","Open")))

Just to make sure: This formula says...

When H2 on the AM2<FACTORS!$H$2,"< 48H?,IF(AM2 IF(AM2='Factors!$H$2,"=' 48H?,>Factors sheet is equal to a non-zero numeric or TRUE value, display:

> 48H*

When H2 on the AM2<FACTORS!$H$2,"< 48H?,IF(AM2 IF(AM2='Factors!$H$2,"=' 48H?,>Factors sheet is equal to zero or FALSE value, display:

Open

Is this right?

 

[FKoenig]

Might help to explain what you want your formula to do.

=IF(AM2<FACTORS!$H$2,"< 48H?,IF(AM2 IF(AM2='Factors!$H$2,"=' 48H?,>Factors!$H$2,"> 48H*","Open"))) contains 2 too many closing parentheses.

 

[FKoenig]

"Open" would also be returned if H2 was empty.

 

[isadoko]

My entire formula is actually checking for three states:
1. Does the value in H2 less than 48 hours
2. ...equal to 48 hours
3. ...greater than 48 hours

That is why my three closing ))). However, some fields H column cells are blank returning #NUM! and that is what want to convert to Open. I understand another approach is using error.type but I have never used this type formula.

Does that help?

 

[FKoenig]

Is cell H2 in a date/time format, or numeric?

 

[Aladin Akyurek]

My entire formula is actually checking for three states:
1. Does the value in H2 less than 48 hours
2. ...equal to 48 hours
3. ...greater than 48 hours

That is why my three closing ))). However, some fields H column cells are blank returning #NUM! and that is what want to convert to Open. I understand another approach is using error.type but I have never used this type formula.

Does that help?

What needs the formula to return when (1), when (2), or when (3) holds?

 

[isadoko]

Pardon all but lost connection and had tasks to complete...

I am actually evaluating two factors in a the life of an event: 1) open time and 2) close time. Sometimes the close time field is blank because the event is still open causing my formula to return #NUM!. What I hope to obtain using if(iserror, is a blank or some substitute text, ie, Open , in place of #NUM!. By the way Factors!$H$2 equals 48:00:00.

Going even deeper, the value in AM is predicated on this solution--specifically total number working hours: http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

As for Error.Type, I found this explanation on Ozgrid: http://www.ozgrid.com/Excel/formula-errors.htm

However, I have so far failed to make it work...will continue trying. It claims to be a better solution than if(iserror.

Another attempt to show complete formula: =IF(AM2<Factors!$H$2,"< 48H",IF(AM2=Factors!$H$2,"= 48H",IF(AM2>Factors!$H$2,"> 48H","")))