How do i get rid of the #VALUE!

[caldinafan]

Night All

I need your help

I built this spreadsheet, and I ended up with #VALUES! error. I have tried getting rid of them by using ISERROR and IFERROR, but that didn't work. Whats the best thing to get rid of these.

Appreciated

See file attached.

The formulas are in columns N through V

[Norie]

The problem is that you have a bunch of values that are text instead of time.

For example :00 is text, it's not a time.

Same with things like :51:35, :13:30, :02 etc, basically anything starting with a :.

You could hide the errors using IFERROR or ISERROR but it's probably better to fix the data.

Where does the data come from?

[caldinafan]

The data was pulled from Avaya CMS. What's the best and quickest way to do it without have to convert those from text?

[adhawan06]

Hi Caldinafan,

Yup the moment i saw AUX in your file database, I got to know that it has pulled out from Avaya (as it's standard codes of break outs ). I have attached a final worksheet. Just used Iferror function and it's perfectly working fine. Could you please check and confirm.

I also used to keep my raw data as it is always as i never try to touch anything in my raw data. You have now both the options.

[caldinafan]

Thanks adhawan06, it worked like a charm.

[adhawan06]

Thanks caldinafan. Glad to help you.

Don't forget to say thanks if someone of us helped you by solving your problem by just adding *Add Reputation and marked this thread as Solved.

Cheers!!!