#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Formula with a # result

Options
✭✭✭✭✭✭
edited 12/09/19

Goal:

I'm basing this "costing" formula off of a cell that is in % format, the format in the "costing" cell is \$.

If Percent8 Cell is x THEN this #

0 through 0.43 = 50

0.44 through 0.90 = 75

0.91 through 1.00 = 100

What is wrong with my formula? I keep getting an #unparaseable message

=IF(AND(Percent8 >= 0, Percent8 <= 0.43), 50, IF(AND(Percent8 >= 0.44, Percemt8 <= 0.90), 75, IF(AND(Percent8 >= 0.91, Percent8 <= 1.00), 100))))

Tags:
«1

• ✭✭✭✭✭✭
Options

You have an M in one of your percents.

=IF(AND(Percent8 >= 0, Percent8 <= 0.43), 50, IF(AND(Percent8 >= 0.44, Percemt8 <= 0.90), 75, IF(AND(Percent8 >= 0.91, Percent8 <= 1.00), 100))))

• ✭✭✭✭✭✭
Options

Sorry, even correct with that it wouldn't work.

• ✭✭✭✭✭✭
Options

if Percent8 is a formulated cell, will it not work?

• ✭✭✭✭✭✭
Options

Should work. Rewriting the formula was easier than troubleshooting it. I tossed in an error if the program fails to assign a grade as well.

=IF(AND(Percent8 >= 0, Percent8 <= 0.43), 50, IF(AND(Percent8 > 0.43, Percent8 <= 0.9), 75, IF(AND(Percent8 > 0.9, Percent8 <= 1), 100,"error")))

I'll think a little more about why yours is wrong. I'll let you know if I come up with anything.

• ✭✭✭✭✭✭
Options

you had 1 to many closing parenthesis.

• ✭✭✭✭✭✭
Options

Just a tip, for smartsheet at least, don't type in the closing parenthesis for the end of the function. The program closes all functions automatically, and it's not worth the time and effort. Just don't put any at the end and it will auto-fill the correct number.

• ✭✭✭✭✭✭
Options

Thanks Luke. This worked, I wonder if not starting the next IF with the # I ended with messed it up? Example 0 - 0.43 and 0.44-0.90 vs  0 - 0.43 and 0.43-0.90

Also,

If in another cell, is it possible to take this formulated result and multiply it by another cell with a formulated result? I'm receiving an #invalid operation error when I attempt this

• ✭✭✭✭✭✭
Options

my formula i gave you didn't account for the percent being blank, sorry

=IF(AND(Percent8 >= 0, Percent8 <= 0.43), 50, IF(AND(Percent8 > 0.43, Percent8 <= 0.9), 75, IF(AND(Percent8 > 0.9, Percent8 <= 1), 100, IF(ISBLANK(Percent8), "", "error"

test that one out. Note I didn't include a single closing parenthesis.

• ✭✭✭✭✭✭
Options

and yes you can do that.

• ✭✭✭✭✭✭
Options

That is a great tip, Luke. Thanks! I always throw my formulas into notepad++ and track the closing parenthesis. This will save me a ton of time!

• ✭✭✭✭✭✭
Options

It gives me an Invalid Operation Error when I use:

=column9 * column10

column 9 & 10 results are based on formulas.

• ✭✭✭✭✭✭
Options

I know! All this time Ive been worried about parenthesis!

• ✭✭✭✭✭✭
Options

Gonna need more information than that then. It is probably a data type issue but without knowing the variables involved I can't help to much.

• ✭✭✭✭✭✭
Options

I hate it when I make a mistake like this, but Smartsheet autoreads blank cells as 0 so this is incorrect. the isblank statement needs to be in the front for it to be effective.

=IF(ISBLANK(Percent8), "", IF(AND(Percent8 >= 0, Percent8 <= 0.43), 50, IF(AND(Percent8 > 0.43, Percent8 <= 0.9), 75, IF(AND(Percent8 > 0.9, Percent8 <= 1), 100, "error"))))

• ✭✭✭✭✭✭
Options

What are the column types of columns 9 and 10 and the one you are inserting this formula in? Are they all text/number columns? Right-click on the column headers and choose properties to determine their type.

This discussion has been closed.