# Using VALUE within an IF formula to covert text to number

Options
✭✭✭✭✭

Hello,

I've determined the issue with this formula is that the cell it is pulling from is formatted as text, not a number. I know I need to use the VALUE function to correct this, but I've tried this many different ways and cannot get it to work. This is my original formula:

=IF([Projected SI %]1 = 0, "0", IF([Projected SI %]1 = "MIN", "5000", ([Projected Vendor Spend]1*([Projected SI %]1))))

If this looks correct can you please help me put the VALUE function in correctly? This is the part trying to multiply by the cell stored as text: ([Projected SI %]1) at the very end.

• ✭✭✭✭✭✭
Options

Hi Tanya,

Try removing the ”” in the numbers.

Here’s one without ”” and with @row instead of row numbers, so you don’t have to think about them.

Try something like this.

```=IF([Projected SI %]@row = 0; 0; IF([Projected SI %]@row = "MIN"; 5000;
[Projected vendor Spend]@row * [Projected SI %]@row))
```

The same version but with the below changes for your and others convenience.

```=IF([Projected SI %]@row = 0, 0, IF([Projected SI %]@row = "MIN", 5000,
[Projected vendor Spend]@row * [Projected SI %]@row))
```

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

=IF([Projected SI %]1 = 0, "0", IF([Projected SI %]1 = "MIN", "5000", [Projected Vendor Spend]1*VALUE([Projected SI %]1)))

See the bold portion above. I also removed a couple of unnecessary parenthesis.

• ✭✭✭✭✭
Options

This gives me an Invalid Value error?

• ✭✭✭✭✭
Options

Sorry, the suggestion from Paul gave me the Invalid Value error.

The suggestion from Andree gives me an Invalid Operation error.

Any ideas?

• ✭✭✭✭✭✭
Options

What is in [Projected SI %]1?

• ✭✭✭✭✭
Options

Another IF formula, the result is 3.5%.

If I just enter in a number in that cell, everything works fine, that is why I assume I need to convert it from text to number?

• ✭✭✭✭✭✭
Options

What is that formula?

• ✭✭✭✭✭
Options

=IF([SI Type]1 = "Standard", IF([Projected Growth]1 >= 0.2001, "4.5%", IF([Projected Growth]1 >= 0.1001, "4.0%", "3.5%")), IF([SI Type]1 = "Fastener", IF([Projected Growth]1 >= 0.1, "2.5%", IF([Projected Growth]1 <= 0.1, "1.5%")), IF([SI Type]1 = "MIN", "MIN", [SI Type]1)))

• ✭✭✭✭✭✭
edited 01/24/20
Options

Ah. Ok. That would be the issue then.

=IF([Projected SI %]1 = 0, "0", IF([Projected SI %]1 = "MIN", "5000", [Projected Vendor Spend]1 * VALUE(SUBSTITUTE([Projected SI %]1, "%", ""))))

(edited to make the update bold)

• ✭✭✭✭✭
Options

Ahhhh so much closer! It gave back \$1,280,699 when it should be \$12,806.99 how would I adjust that?

• ✭✭✭✭✭
Options

That worked! I cannot thank you enough Paul!!!!!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• ✭✭✭✭✭
Options

Paul,

A similar one here....

=IF(ISBLANK([Rebate ROI]1), "0", (((([T-1 min dollar]1 * [Tier 1 %]1) - \$[Projected Rebate \$]1) + (([T-1 min dollar]1 * [SI Achieved]1) - \$[Projected SI \$]1)) / [Additional Spend Needed]1))

This part [SI Achieved]1) is the same situation, I tried to add the value substitute but am unsure of the layout, parentheses, etc. The formula in that cell is:

=IF(ISBLANK([Rebate ROI]1), "0", IF(\$[SI Type]1 = "Standard", IF((([T-1 min dollar]1 - \$[2019 Spend]1) / \$[2019 Spend]1) >= 0.2001, "4.5%", IF((([T-1 min dollar]1 - \$[2019 Spend]1) / \$[2019 Spend]1) >= 0.1001, "4%", "3.5%")), IF(\$[SI Type]1 = "Fastener", IF((([Rebate ROI]1 - \$[2019 Spend]1) / \$[2019 Spend]1) >= 0.1, "2.5%", IF((([T-1 min dollar]1 - \$[2019 Spend]1) / \$[2019 Spend]1) < 0.1, "1.5%")), \$[SI Type]1)))

And it also returns a percentage formatted as text.

• ✭✭✭✭✭
Options

I got this far and am getting a percent, not sure when I add the /100 how many parentheses I need?

=IF(ISBLANK([Rebate ROI]1), "0", (((([T-1 min dollar]1 * [Tier 1 %]1) - \$[Projected Rebate \$]1) + (([T-1 min dollar]1 * (VALUE(SUBSTITUTE([SI Achieved]1, "%", "")) - \$[Projected SI \$]1)) / [Additional Spend Needed]1))))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!