nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Text to Number Issue

Angela Logie

So the below formula however its resulting in a text result, i.e. left aligned. I'm then trying to run an AVG/Collect or AverageIF formula on this column however its providing a #DIVIDE BY ZERO result.

Any idea/thoughts on how to resolve this?

Below is the formula resulting in a 'text' result that I cant run a count/avg formula on:

=IF(Status@row = "1.Not Started", "0%", IF(Status@row = "2.Extraction in Progress", "20%", IF(Status@row = "3.Transformation in Progress", "40%", IF(Status@row = "4.Pre-Load Approval Pending", "50%", IF(Status@row = "5.Load in Progress", "75%", IF(Status@row = "6.Post-Load Approval Pending", "80%", IF(Status@row = "7.Complete", "100%")))))))

I've tried changing the formula to the below i.e. removing the %'s:

=IF(Status@row = "1.Not Started", "0", IF(Status@row = "2.Extraction in Progress", "20", IF(Status@row = "3.Transformation in Progress", "40", IF(Status@row = "4.Pre-Load Approval Pending", "50", IF(Status@row = "5.Load in Progress", "75", IF(Status@row = "6.Post-Load Approval Pending", "80", IF(Status@row = "7.Complete", "100")))))))

But this is still resulting in a left aligned text result. So I then created another column to convert the above result into a value using the below formula:

=VALUE([% Complete Convert]@row)

But I'm assuming there must be a better way of doing this?

Thanks in advance

Angela

Find more posts tagged with

Formulas

collect

avg

averageif

text to number

Accepted answers

DKazatsky2

Hi @Angela Logie,

Try removing the quotes around the numerical value in the formula, that should tell the system to not treat the numbers as strings.

=IF(Status@row = "1.Not Started", 0, IF(Status@row = "2.Extraction in Progress", 20, IF(Status@row = "3.Transformation in Progress", 40, IF(Status@row = "4.Pre-Load Approval Pending", 50, IF(Status@row = "5.Load in Progress", 75, IF(Status@row = "6.Post-Load Approval Pending", 80, IF(Status@row = "7.Complete", 100)))))))

Hope this helps,

Dave

Paul Newcome

Smartsheet reads percentages as a part of a whole or a part of one.

10% = 0.10

25% = 0.25

83% = 0.83

100% = 1

So instead of outputting 20, output .2 then format the column to show as percentages.

All comments

DKazatsky2

Hi @Angela Logie,

Try removing the quotes around the numerical value in the formula, that should tell the system to not treat the numbers as strings.

Hope this helps,

Dave

Angela Logie

So this worked great, it resulted in a number i.e. 100 as a value and right aligned. The problem I now have is when I add the % to that column, it changes from 100 to 10,000%

Do you know how I would fix that?

And THANK YOU!

Paul Newcome

Smartsheet reads percentages as a part of a whole or a part of one.

10% = 0.10

25% = 0.25

83% = 0.83

100% = 1

So instead of outputting 20, output .2 then format the column to show as percentages.

Angela Logie

This worked a treat!!! Thank you so much!!!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.