Summarize a set of number values with text, but some values are blank

malden

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!

KPH

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, ")

malden

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