Trying to figure out % a certain word was chosen from a drop down

Goodie3133

I want to say what percent each office has of promoters in each section then in the entire sheet.

ss question.PNG

Thank you!!

Genevieve P.

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

Genevieve P.

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)

See: Conditional Formatting

Genevieve P.

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

Goodie3133

@Genevieve P.

It worked! Thank you so much!!!!

Genevieve P.

No problem! I'm glad I could help 🙂

Goodie3133

@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!

Genevieve P.

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)

See: Conditional Formatting

Genevieve P.

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