Formula with a # result

Laura

 

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))))

L_123

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))))

Laura

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

Laura

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

L_123

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.

L_123

you had 1 to many closing parenthesis.

L_123

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. 

Laura

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

L_123

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.

L_123

and yes you can do that.

Mike Wilday

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! 

Laura

It gives me an Invalid Operation Error when I use:

=column9 * column10

column 9 & 10 results are based on formulas.

 

Laura

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

L_123

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.

L_123

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.

Mike Wilday

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.