4 Replies Latest reply on Jul 10, 2017 10:07 AM by Jim Dehner

# Round Then Display As a String

Hello,

I am having a difficult time displaying 1 decimal consistently for all outputs. Attached is a workbook with context, but the below portion of the calculation is what I believe needs to be tweaked in order to get the desired result.

Max(IF RIGHT(STR(ROUND([Final Net Back % to display],3)),8) == "000001"

THEN

Ifnull(Left(STR(ROUND([Final Net Back % to display]*100,2)), Find(STR(ROUND([Final Net Back % to display],2)),".") + 2)+"%","N/A")

ELSE

Ifnull(Left(STR(ROUND([Final Net Back % to display]*100,2)),Find(STR(ROUND([Final Net Back % to display],2)),".") + 2)+"%","N/A")

Thank you!

• ###### 1. Re: Round Then Display As a String

Hi Kevin;

I can't say I understand exactly what your formula is trying to do, but one thing that stood out to me is your check for a 6-digit value when it appears you are creating 8-digit ones:

Max(IF RIGHT(STR(ROUND([Final Net Back % to display],3)),8) == "000001"

Should this be:

Max(IF RIGHT(STR(ROUND([Final Net Back % to display],3)),8) == "00000001"

Your THEN and ELSE parts of the equation are the same, too; maybe your actual code varies?

Could you give us examples of where you see the rounding working inadequately and what you expect to see?

Thanks!

Thought: Are you checking for a "6-digit" value because you're actually checking for 100%? If this is the case, will your calculation return 00000100 or 000001? I am still a little mystified as to why your THEN and ELSE parts are identical.

• ###### 2. Re: Round Then Display As a String

Hi Kevin

I'm not certain I understand what you are trying to show

but have you tried  the portion of your formula that determines the Str value of the Final Net Back % as shown below

str((round(avg([Final Net Back % to display]) * 100,1)))

you would have to wrap it your ifnull statements

here is the result I got in the column labelled "Try this" it is shown as a discrete value

Let me know if this helps

Jim

1 of 1 people found this helpful
• ###### 3. Re: Round Then Display As a String

Thank you for helping me figure out how to display 1 decimal rounded and the null values as "NA". • ###### 4. Re: Round Then Display As a String

Glad to help out Kevin -

Thanks for the badge - always appreciated

Jim