Calculating Percentages in Smartsheet based on the parent value
I have two columns in my sheet. One is for the data and the other is for the percentage. In the data column, I have 5 rows with numbers 1, 2, 3, 4, and 5. The total of these numbers is 15, which is the parent value. I want to use a single formula in the percentage column to calculate the percentage of each of these 5 rows based on the parent value. I want to be able to drag this formula or just change it to a formula column for all the other rows. The expected result for the 5 rows should be 6.67%, 13.33%, 20%, 26.67%, 33.33% respectively as shown in the calculation below.
first child row will be (1/15)*100 = 6.67%,
second child row will be (2/15)*100 = 13.33%,
third child row will be (3/15)*100 = 20%,
fourth child row will be (4/15)*100 = 26.67%,
fifth child row will be (5/15)*100 = 33.33%.
Answers
-
@Anoop Phago I think this will work
=data@row / parent(data@row) and then format that column to percent.
might need to put that formula in "iferror"
=iferror( data@row / parent(data@row) , "")
-
@Samuel Mueller thanks for the reply, but I have one column with child-parent in the same column (i.e Data Column), and I have added the parent row, adding up the child rows. I need to calculate the percentage in the next column (i.e Percentage Column). There are various more sites, and I want to be able to drag that formula from top to bottom.
-
@Anoop you should be able to take this formula and put it into the percentage column and turn it into a column formula.
=[data column]@row / parent([data column]@row)
you may want it like this if you area getting division errors:
=iferror([data column]@row / parent([data column]@row), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!