Numerical Values (Points) for Dropdown Fields and totaling the Numerical Values

BristolCVN

Hi,

I am working on a sheet where I have 6 fields that require the user to select a value. Each field's value will have a numerical (points) equivalent. I have created the 6 dropdown fields and was successfully able to build a "helper" column next to each, using an IF statement to populate the Helper column with a numerical value based on the user's selection. However, when I try to total all the "Helper" columns up, I'm getting issues with the decimal points and or incorrect totals. In my "IF" statements, should the numerical values be in "quotes"? Should I include all decimal points? The numerical values include, 0, .25, .5, and 1.

Also, in one of my columns, the user can select multiple values and each value has a numerical equivalent, how can I "add" the multiple values from that cell and have it totaled to be added to the Grand Total?

In the screenshot below, this is one of my data columns, that has 4 values and numerical equivalents. Guess I should start with is the right format for that?

Thanks in advance!

Client Meeting Required is the data field and CMR Helper is where the formula is.

Carson Penticuff

Ok, you have a few different questions here, so let's tackle them one at a time.

In my "IF" statements, should the numerical values be in "quotes"?

No, you should not enclose numerical values in quotes. Doing so will cause these values to be treated as text. You can "convert" these values back to numbers when referencing them from another formula by using the VALUE() function, but simply omitting the quotes will allow them to stay numbers and avoid the extra step.

Should I include all decimal points? The numerical values include, 0, .25, .5, and 1.

This really shouldn't matter. If you use .25 in a formula, the leading zero should be added by default. Just be sure to set the column type to Text/Number in the column properties dialogue and then use the "increase decimal" icon in the toolbar expand the decimal to 2 places for the entire column.

Also, in one of my columns, the user can select multiple values and each value has a numerical equivalent, how can I "add" the multiple values from that cell and have it totaled to be added to the Grand Total?

The basic idea is to use a combination of SUM(), IF(), and HAS() to get the total for the helper column. Here is a example:

And here is the formula for the helper column:

=IF(ISBLANK([Animals]@row), "N/A", SUM(IF(HAS([Animals]@row, "Dog"), 1, 0), IF(HAS([Animals]@row, "Cat"), 10, 0), IF(HAS([Animals]@row, "Chicken"), 100, 0), IF(HAS([Animals]@row, "Penguin"), 1000, 0)))

Here it is broken out: (makes it much easier to visualize)

As far as the incorrect totals you mention, I would need a bit more information to assist there. The screenshot you posted seems correct, I see no issue at all.

To total all of your helper columns, you would use something like:

=SUM([Helper 1]@row, [Helper 2]@row, [Helper 3]@row, [Helper 4]@row, [Helper 5]@row, [Helper 6]@row)

Carson Penticuff

Ok, you have a few different questions here, so let's tackle them one at a time.

In my "IF" statements, should the numerical values be in "quotes"?

No, you should not enclose numerical values in quotes. Doing so will cause these values to be treated as text. You can "convert" these values back to numbers when referencing them from another formula by using the VALUE() function, but simply omitting the quotes will allow them to stay numbers and avoid the extra step.

Should I include all decimal points? The numerical values include, 0, .25, .5, and 1.

This really shouldn't matter. If you use .25 in a formula, the leading zero should be added by default. Just be sure to set the column type to Text/Number in the column properties dialogue and then use the "increase decimal" icon in the toolbar expand the decimal to 2 places for the entire column.

Also, in one of my columns, the user can select multiple values and each value has a numerical equivalent, how can I "add" the multiple values from that cell and have it totaled to be added to the Grand Total?

The basic idea is to use a combination of SUM(), IF(), and HAS() to get the total for the helper column. Here is a example:

And here is the formula for the helper column:

=IF(ISBLANK([Animals]@row), "N/A", SUM(IF(HAS([Animals]@row, "Dog"), 1, 0), IF(HAS([Animals]@row, "Cat"), 10, 0), IF(HAS([Animals]@row, "Chicken"), 100, 0), IF(HAS([Animals]@row, "Penguin"), 1000, 0)))

Here it is broken out: (makes it much easier to visualize)

As far as the incorrect totals you mention, I would need a bit more information to assist there. The screenshot you posted seems correct, I see no issue at all.

To total all of your helper columns, you would use something like:

=SUM([Helper 1]@row, [Helper 2]@row, [Helper 3]@row, [Helper 4]@row, [Helper 5]@row, [Helper 6]@row)

BristolCVN

Thank you Carson! Will let you know the results.