Showing percentages as whole numbers in Dynamic View

User251

Hi, I have a Dynamic View of a sheet that uses percentages. They show up, however, with 13 decimal places even though they are whole numbers in the original sheet. How can I fix this issue and show the percentages as whole numbers in Dynamic View?

The percentage is calculated in the original sheet using the formula IFEEROR(([Cell]1 - [Cell]2) / [Cell]1, "NA")

Thanks.

Paul Newcome

My apologies. I forget that even in my own solutions and have to go back and add it in. Use the decimal places in the regular ROUND function like so:

=IFEEROR(ROUND(([Cell]1 - [Cell]2) / [Cell]1, 2), "NA")

Paul Newcome

Wrap the math portion in a ROUND function like so:

=IFEEROR(ROUND(([Cell]1 - [Cell]2) / [Cell]1), "NA")

User251

Thank you so much Paul for the quick response. I tried adding Round but it gives me 0%.

I tried the roundup function to 1 decimal place, however, it is giving me the error UNPARSEABLE.

Paul Newcome

My apologies. I forget that even in my own solutions and have to go back and add it in. Use the decimal places in the regular ROUND function like so:

=IFEEROR(ROUND(([Cell]1 - [Cell]2) / [Cell]1, 2), "NA")

User251

Thank you so much @Paul Newcome . That fixed the issue. thanks again!

Paul Newcome

Happy to help. 👍️

User251

@Paul Newcome I noticed that if I change the 2 to 1 (i.e. if I want to round to 1 decimal place), the percentage becomes 0. Am I doing something wrong?

Paul Newcome

@User251 Are you able to provide the exact formula you are working with as well as maybe some screenshots of the source data?

User251

@Paul Newcome The screenshots below show the formula that I used in each column and the formula outcomes in the first and second rows.

I usually use the Increase Decimals and Decrease Decimal options (highlighted in yellow below). The issue is with Dynamic View which always shows the percentage with 13 decimal places even if I use Decrease and Increase Decimlas options.

Paul Newcome

@User251 That is because 3% is actually 0.03. Because 3 is less than 5 it gets rounded down. Try putting in some numbers that will be greater than 5% to see if it starts working then.

User251

@Paul Newcome The example in the first row rounded 35.65% to 40% in the column (Rounding to 1 Decimal place) and 36% (in the column rounding to 2 decimal places).

I am a bit confused. I was expecting 3.32% to become 3.3% (in the column Rounding to 1 decimal place) and 3% (in the column Rounding to 2 decimal places).

I also expected 35.65% to become 35.7% and 36% when rounded to 1 and 2 decimal places respectively.

in the Smartsheet Formula Examples sheet, the formula works the way I want my numbers to appear but it works differently when I apply it.

(https://app.smartsheet.com/sheets/WrgcP4h6mx5HPXh92GQRRcvpQv64grGMxQ5xjqV1?view=grid)

Paul Newcome

@User251 It is actually working as expected. Here is a quick explanation:

Percentages are actually a part of a whole. 1 whole. This means that 100% is actually 1. 50% is actually 0.50. 35.65% is actually 0.3565.

I think what you want is to actually move the rounding further into the decimal places such as 3 or 4 instead of 1 or 2.

User251

Thank you @Paul Newcome! That was very helpful.

Paul Newcome

@User251 Happy to help. 👍️