Showing percentages as whole numbers in Dynamic View
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.
Best Answer
-
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")
Answers
-
Wrap the math portion in a ROUND function like so:
=IFEEROR(ROUND(([Cell]1 - [Cell]2) / [Cell]1), "NA")
-
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.
-
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")
-
Thank you so much @Paul Newcome . That fixed the issue. thanks again!
-
Happy to help. 👍️
-
@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?
-
@User251 Are you able to provide the exact formula you are working with as well as maybe some screenshots of the source data?
-
@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.
-
@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.
-
@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)
-
@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.
-
Thank you @Paul Newcome! That was very helpful.
-
@User251 Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives