IF Statement Malformation: Type Mismatch where Number Required

[stennis]

I'm trying to show how long an outage lasted with the following statement. my boss wants to see the outcome as '1 day(s) 2 hour(s) 36 minute(s)'

The formula accepts everything until I add the Endif. then I get the malformed statement error in the subject. Thoughts?

'' IF Average([Outage Duration (Seconds)]) < 3600
THEN
//Convert from Seconds to Minutes
"0 Day(s) 0 Hour(s) " + ToString(ToNumber(Average([Outage Duration (seconds)])/60)) + " Minute(s) "

ELSEIF Average([Outage Duration (seconds)]) < 86400
THEN
//Convert from Seconds to Hours
"0 Day(s) " + ToString(ToNumber(Average([Outage Duration (seconds)])/3600)) + " Hour(s) " + ToString(ToNumber(Average([Outage Duration (seconds)])/3600)/60) + " Minute(s)"

ELSE
//Convert from Seconds to Days
ToString(ToNumber(Average([Outage Duration (seconds)]) / 86400)) + 'Day(s)' + ToString(ToNumber(Average([Outage Duration (seconds)]) / 86400) / 3600) + "Hour(s) "+ ToString((ToNumber(Average([Outage Duration (seconds)]) /86400) / 3600) / 60) + "Minute(s)"

endif  "

Thanks for the help! 

[DavidP]

You should use ToString rather than ToNumber

[fmvizcaino]

Hi @stennis ,

You only need to add a ToString function before each ToNumber function.

Tostring(ToNumber(Average([Outage Duration (seconds)])/60))

Best,

Fernando Vizcaino

[stennis]

my appologies! this is what I had the first time. I accidentally posted my trying to fix it. It still wouldn't take. am I missing a ToString someplace? I edited the original post to provide the original formula I was using below that I was hoping to find answers on

'' IF Average([Outage Duration (Seconds)]) < 3600
THEN
//Convert from Seconds to Minutes
"0 Day(s) 0 Hour(s) " + ToString(ToNumber(Average([Outage Duration (seconds)])/60)) + " Minute(s) "

ELSEIF Average([Outage Duration (seconds)]) < 86400
THEN
//Convert from Seconds to Hours
"0 Day(s) " + ToString(ToNumber(Average([Outage Duration (seconds)])/3600)) + " Hour(s) " + ToString(ToNumber(Average([Outage Duration (seconds)])/3600)/60) + " Minute(s)"

ELSE
//Convert from Seconds to Days
ToString(ToNumber(Average([Outage Duration (seconds)]) / 86400)) + 'Day(s)' + ToString(ToNumber(Average([Outage Duration (seconds)]) / 86400) / 3600) + "Hour(s) "+ ToString((ToNumber(Average([Outage Duration (seconds)]) /86400) / 3600) / 60) + "Minute(s)"

endif ''

[fmvizcaino]

Hi @stennis ,

I think your Average([Outage Duration (Seconds)]) data type is set to string, maybe a select tool before your formula will solve your issue.

Best,

Fernando Vizcaino

[DannyS]

Hi @stennis ,

It looks like the statement after your ELSE is what's causing the issue. Since your end result for this conditional statement will be a string (i.e. text) data type, I would ensure that your last statement:

ToNumber(Average([Outage Duration (seconds)]) / 86400) + 'Day(s)' + ToNumber(Average([Outage Duration (seconds)]) / 86400) / 3600 + "Hour(s) "+ ToNumber(Average([Outage Duration (seconds)]) /86400) / 3600 / 60 + "Minute(s)"

truly ends as a string data type. You can simply replace the "ToNumber" with "ToString" and that should solve the issue.

[stennis]

Changing the 'ToNumber" to "ToString' gives me a malformed statement error and still won't accept the endif. but now the malformed statement doesn't give a reason. I am parring down the data to provide a smaller workbook that I can share

[fmvizcaino]

Hi @stennis ,

I think your problem is related to the [Outage Duration (Seconds)] data type, you need to set to a integer or double.

Also I would suggest you to remove the average function, you only need that function if you want to calculate the average between 2 values or more, i.e. Average( [field1],[field2] ). If you need to calculate the average for a group of rows, you will need to summarize it first.

Here is an adapted function written with my suggestion of changing data types.

Best,

Fernando Vizcaino

[DavidP]

As Fernando mentioned, Average in this case is redundant and so is Tonumber. You can also see from the results that the output isn't very user friendly.

We'll need some rounding. Perhaps the formulas need a bit of rethinking. I'll have a play around and get back to you.

[stennis]

I'm sorry for the delay in responses. I'm waiting for my large data import from the servers to cash so I can pare it down and give you my work flow