Formula Error In Excel 2003

[janinep]

I am developing a report for one of our projects to show agent out of adherence time. I am developing this in excel 2007. The project I am developing it for uses excel 2003. Everything is fine, except when they sort the MTD tab by manager. All the data becomes error messages. Here are the formulas I am using:

=IFERROR(VLOOKUP(A2,'1'!$A$2:$R$203,17,FALSE),"") - This formula is being used to look up the agent's out of adherence time in the form of seconds for the month on a day - by - day basis and totaled at the end of the row.

=IFERROR(TEXT(C2/86400,CHOOSE(MATCH(C2,{0,60,3600},1),":ss","m:ss","[h]:mm:ss")),"") - this formula is being used to convert the seconds to hour:minute:seconds time format

When I check the formulas in excel 2003, it changes each formula by adding _xlfs. before each one so the formulas look like this:

=_xlfs. IFERROR(VLOOKUP(A2,'1'!$A$2:$R$203,17,FALSE),"") and
=_xlfs. IFERROR(TEXT(C2/86400,CHOOSE(MATCH(C2,{0,60,3600},1),":ss","m:ss","[h]:mm:ss")),"")

What can I do to prevent this?

Thanks for the help.

Janine

 

[VoG]

IFERROR is not available in Excel 2003. Try like this

IF(ISERROR(VLOOKUP(A2,'1'!$A$2:$R$203,17,FALSE)),"",VLOOKUP(A2,'1'!$A$2:$R$203,17,FALSE))

 

[janinep]

Thank you very much for the help on that! That worked great for the seconds, but how do I get the error out of the formula that converts the seconds to "Hour:Minute:Seconds" format? Again, this is the formula I am using for that:

=IFERROR(TEXT(C8/86400,CHOOSE(MATCH(C8,{0,60,3600},1),":ss","m:ss","[h]:mm:ss")),"")

Thanks again!

Janine

 

[schielrn]

Try:

=if(iserror(TEXT(C8/86400,CHOOSE(MATCH(C8,{0,60,3600},1),":ss","m:ss","[h]:mm:ss"))),"",TEXT(C8/86400,CHOOSE(MATCH(C8,{0,60,3600},1),":ss","m:ss","[h]:mm:ss")))

Hope that helps.

 

[janinep]

Thank you so much for the help, everyone! Both suggestions worked great! My boss thinks I'm a genius now!!

Janine

 

[schielrn]

Thank you so much for the help, everyone! Both suggestions worked great! My boss thinks I'm a genius now!!

Janine

Thats what this site is for.

I've learned everything I know about Excel in the less than 4 years have been here.