Summarize a set of number values with text, but some values are blank
Hi all,
I am creating a text summary that pulls numbers from other columns. It's for the results of a ballot, so I want it to look like this:
2 approve, 4 reject, 5 abstain
And the formula looks like this:
=([Vote count: Approve]@row + " approve, " + [Vote count: Reject]@row + " reject, " + [Vote count: Abstain]@row + " abstain")
However, I want it to ignore the instructions if a value is blank, so that I don't end up with some scenarios like this if, say, 11 people approved, and there's no value entered for "reject" or "abstain."
11 approve, reject, abstain
In that case, I want it to just say "11 approve".
So, is there an elegant way to edit my formula to include text only if it's associated with a number?
Thanks in advance!
Answers
-
I'd be inclined to use IF functions to only output the part of the sentence if the value in the column is >0
=IF([Vote count: Approve]@row > 0, [Vote count: Approve]@row + " approve, ")
would put "7 approve, " if there was 7 in the approve column, and nothing if it was blank or 0.
The other two columns could be added like this:
=IF([Vote count: Approve]@row > 0, [Vote count: Approve]@row + " approve, ") + IF([Vote count: Reject]@row > 0, [Vote count: Reject]@row + " reject, ") + IF([Vote count: Abstain]@row > 0, [Vote count: Abstain]@row + " abstain, ")
You might see some extra 0s if both of the first columns are blank or 0. So, the result would be "08 abstain, " rather than "8 abstain, ".
This could be avoided by adding another IF around the first two parts so these are only evaluated if the sum of both is greater than 0.
=IF([Vote count: Approve]@row + [Vote count: Reject]@row > 0, IF([Vote count: Approve]@row > 0, [Vote count: Approve]@row + " approve, ") + IF([Vote count: Reject]@row > 0, [Vote count: Reject]@row + " reject, ")) + IF([Vote count: Abstain]@row > 0, [Vote count: Abstain]@row + " abstain, ")
-
@KPH Thanks so much for taking the time to look at this. Thinking about it more, I did not want to deal with managing the extra commas as well. I ended up deciding to use zeroes in place of blank fields, and also add line breaks in the formula so that the information is always easy to read at a glance.
=([Vote count: Approve]@row + " approve, " + UNICHAR(10) + [Vote count: Approve w/comments]@row + " approve w/comments, " + UNICHAR(10) + [Vote count: Reject]@row + " reject, " + UNICHAR(10) + [Vote count: Abstain]@row + " abstain"))
So now it reads like this:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!