Trying to figure out % a certain word was chosen from a drop down
I want to say what percent each office has of promoters in each section then in the entire sheet.
Thank you!!
Best Answers
-
Hi @Goodie3133
Where do you want this percent to appear? In your screen capture it looks like you have Hierarchy with Parent rows, and you're looking to see the percent of the Children rows, is that correct?
If so, you can place this formula in the Parent's cell for your Dropdown Column:
=COUNTIF(CHILDREN(), "Promoter") / COUNT(CHILDREN())
Then you can format the cell to be a % type of cell using the formatting toolbar at the top of the sheet. Let me know if this makes sense!
Cheers,
Genevieve
-
Hi @Goodie3133
You can set Conditional Formatting rules on that column in order to change the colour of the cell or the text based on the number it outputs. Keep in mind you'll want to set the criteria as a Decimal (ex. 80% is 0.8)
Answers
-
Hi @Goodie3133
Where do you want this percent to appear? In your screen capture it looks like you have Hierarchy with Parent rows, and you're looking to see the percent of the Children rows, is that correct?
If so, you can place this formula in the Parent's cell for your Dropdown Column:
=COUNTIF(CHILDREN(), "Promoter") / COUNT(CHILDREN())
Then you can format the cell to be a % type of cell using the formatting toolbar at the top of the sheet. Let me know if this makes sense!
Cheers,
Genevieve
-
It worked! Thank you so much!!!!
-
No problem! I'm glad I could help 🙂
-
@Genevieve P. My goal for this sheet is to show Promoter status in the child account then have a status table on the top of each sheet to show where the total of each status is - in my mind something like this. And the percent from the sheet in light blue. Is that possible?
I tried this formula on the parent line next to the one that worked for promoter earlier and tried to reference the same child columns to get a percent of the other options (Distractor, unknown, Passive) and I get #divide by zero.
If you have a better way for me to demonstrate this I am also very open. THanks!
-
Hi @Goodie3133
You can set Conditional Formatting rules on that column in order to change the colour of the cell or the text based on the number it outputs. Keep in mind you'll want to set the criteria as a Decimal (ex. 80% is 0.8)
-
Hi @Goodie3133
My apologies! I didn't see that you had edited the comment from the original question.
In this instance, you'll want to make sure you're referencing the one Parent cell inside of the Children function.
Ex:
CHILDREN([Distractor, Promoter, Passive, Unknown]@row)
This says, look at the children from this cell.
In your formula, you're selecting the actual children range, but the function will do that for you. Since you're referencing a child row that has no children in your formula, the COUNT is 0 which is why you're getting that error.
Try this instead:
=COUNTIF(CHILDREN([Distractor, Promoter, Passive, Unknown]@row), "Unknown") / COUNT(CHILDREN([Distractor, Promoter, Passive, Unknown]@row)
Does that work and make sense?
Cheers!
Genevieve
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!