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
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))))
Comments
-
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))))
-
Sorry, even correct with that it wouldn't work.
-
if Percent8 is a formulated cell, will it not work?
-
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.
-
you had 1 to many closing parenthesis.
-
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.
-
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
-
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.
-
and yes you can do that.
-
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!
-
It gives me an Invalid Operation Error when I use:
=column9 * column10
column 9 & 10 results are based on formulas.
-
I know! All this time Ive been worried about parenthesis!
-
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.
-
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"))))
Sorry about that.
-
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.
