Percentage

Hello!

I have a question, is it possible to create a percentage for symbols? I've been trying to create a percentage of items that are high priority, which would be displayed as a red flag. This would all be displayed in both sheets, reports and on my dashboard.

Thanks for all the help in advance.

Find more posts tagged with

Workspaces

Formulas

Comments

J. Craig Williams

If you can define the corollary between the symbol and its percentage value for your needs, then yes.

Symbols have a text value, not a numeric one.

Craig

Paul Newcome

Just to expand on what J. said...

MOST symbols use a text value. The "Flags" symbols (red flags and single stars) are written like a check box with 1 being true and 0 being false. (I learned this the hard way, banging my head against a wall trying to figure out something very similar).

I typically use a COUNTIF/COUNT statement then multiply by 100 and add the % at the end something like this...

=(COUNTIF([Flag Column]:[Flag Column], 1) / COUNT([Flag Column]:[Flag Column])) * 100 + "" + "%"

This can also work with the Symbols that use text values. Simply replace the 1 with the text you want a percentage of. You can get the text value by clicking the dropdown arrow in the cell and just typing in the words you see next to the corresponding version of the symbol. ("Yellow", "Red", "Half", "Full", etc...)

You can also add text to the beginning to be displayed in the cell with the percentage automatically updating:

="Percent of High Priority Tasks = " + (COUNTIF([Flag Column]:[Flag Column], 1) / COUNT([Flag Column]:[Flag Column])) * 100 + "" + "%"

This would be displayed in the cell as: Percent of High Priority Tasks = ##%

with the ## portion being automatically updated by the formula tucked in the middle.

J. Craig Williams

I misread the original post. Apologies.

Craig

Paul Newcome

You were spot on about the being able to define the value of the symbol part.

I just wanted to try to save anyone else the pain of finding out the hard way about the other part concerning those two particular "Flag" Symbols like I did considering EVERY OTHER SYMBOL is a text value. Hahaha

J. Craig Williams

No, I really read it wrong.

I was thinking something like

if red, 10%, if blue 37%, if green ...

Harvey Balls and 5 stars symbol at least have a count.

Craig

Paul Newcome

Ah. Got it.

With Harveys and 5 Stars though, you still have to use the text value even though it represents a number. "One", Two", "Three Quarters", "Full", etc...

They're also case sensitive as well. For example: With the 5 Stars, "One" works, but "one" or "ONE" does not.

Either way, using symbol references in formulas can be a pain if you aren't careful.

Let's just hope the original poster is able to sift through our back-and-forth and get figured out what they needed. Haha

J. Craig Williams

Smartsheet got rid of case sensitivity sometime in the near-recent past. Or stopped trying. Or maybe it never worked.

This

=IF([Column4]7 = "ONE", "yes", "no")

will return "yes" when the value is "One"

Craig

Sign in to join the conversation!

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…