How to add the first digit in a cell along a row

I used smartsheet to do a survey. We used numerical rankings with a description, (for example: 1 = Not familiar, 10=Very Familiar) as drop down choices in the form. Now, we want to add and average various columns that contain the responses. Is there a way to do that? Just capture the first digit vs the verbiage so we can add and average? I'd prefer not to do the entire row, but group columns into categories and get the average for each category.

See snip below

thanks in advance!

Find more posts tagged with

Formulas

Accepted answers

DKazatsky2

Hi @Afarrar,

My suggestion is to make a "helper" column that will hold just the numbers for the categories and then you can get an average against those columns.

For instance, create a column called "Critical thinking value" and use the following formula. This new column can be hidden if need be.

=IFERROR(VALUE(LEFT([Critical Thinking]@row, FIND(" ", [Critical Thinking]@row) - 1)), "")

Then, to get the average you can use this: =AVG([Critical thinking value]:[Critical thinking value])

Rinse and repeat for each column/category.

Hope this helps,

Dave

Janae G.

A simple formula for extracting the first digit from the text is

=LEFT([Column Name]@row, 1) where 1 tells the LEFT function to return just the leftmost character

However, since some of the numbers vary in length, you will need to modify this to account for the number 10 for example, which has two digits. This can be done by telling the LEFT function to look for a space:

=LEFT([Column Name]@row, FIND(" ", [Column Name]@row)-1)

Here FIND searches for a space in the text, and returns the position of the space minus one so that the LEFT function will capture the correct number of digits.

Then, since it sounds like you will need Smartsheet to recognize the extracted characters as numbers instead of text, surround the previous formula with the VALUE function:

=VALUE(LEFT([Column Name]@row, FIND(" ", [Column Name]@row)-1))

Perhaps the simplest way to implement this would be to create a helper column for each of your survey questions that extracts the number. You could hide these in the sheet if you want to make it cleaner visually.

Then to find averages, you can create another helper column for each category or group of responses you want to find an average for and send it whichever helper columns you want to include in the group:

=AVG([Column1]@row, [Column2]@row, [Column3]@row, …) where each column is the helper column

The above formula would find the average for a group for an individual survey entry. You could also find averages for a column or group of columns in a sheet summary field with a similar formula, but by referencing the entire column instead of @row, ex. [Column1]:[Column1].

Hope this long-winded response helps!

All comments

DKazatsky2

Hi @Afarrar,

My suggestion is to make a "helper" column that will hold just the numbers for the categories and then you can get an average against those columns.

For instance, create a column called "Critical thinking value" and use the following formula. This new column can be hidden if need be.

=IFERROR(VALUE(LEFT([Critical Thinking]@row, FIND(" ", [Critical Thinking]@row) - 1)), "")

Then, to get the average you can use this: =AVG([Critical thinking value]:[Critical thinking value])

Rinse and repeat for each column/category.

Hope this helps,

Dave

Janae G.

A simple formula for extracting the first digit from the text is

=LEFT([Column Name]@row, 1) where 1 tells the LEFT function to return just the leftmost character

=LEFT([Column Name]@row, FIND(" ", [Column Name]@row)-1)

Here FIND searches for a space in the text, and returns the position of the space minus one so that the LEFT function will capture the correct number of digits.

Then, since it sounds like you will need Smartsheet to recognize the extracted characters as numbers instead of text, surround the previous formula with the VALUE function:

=VALUE(LEFT([Column Name]@row, FIND(" ", [Column Name]@row)-1))

=AVG([Column1]@row, [Column2]@row, [Column3]@row, …) where each column is the helper column

Hope this long-winded response helps!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.