Using VALUE within an IF formula to covert text to number
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.
Best Answers

Paul Newcome ✭✭✭✭✭
=IF([Projected SI %]1 = 0, "0", IF([Projected SI %]1 = "MIN", "5000", [Projected Vendor Spend]1 * (VALUE(SUBSTITUTE([Projected SI %]1, "%", "")) / 100)))
That was my mistake. I forgot to account for the fact that we are working with percentages.

Paul Newcome ✭✭✭✭✭
=IF([Projected SI %]@row = 0, "0", IF([Projected SI %]@row = "MIN", "5000", [Projected Vendor Spend]@row * VALUE(SUBSTITUTE([Projected SI %]@row, "%", "")) / IF([SI Type]@row = "Standard", 100, 1)))
See how this works...
Answers
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.
The same version but with the below changes for your and others convenience.
Depending on your country you’ll need to exchange the comma to a period and the semicolon to a comma.
Did it work?
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
=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.
This gives me an Invalid Value error?
Sorry, the suggestion from Paul gave me the Invalid Value error.
The suggestion from Andree gives me an Invalid Operation error.
Any ideas?
What is in [Projected SI %]1?
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?
What is that formula?
=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)))
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)
Ahhhh so much closer! It gave back $1,280,699 when it should be $12,806.99 how would I adjust that?
That worked! I cannot thank you enough Paul!!!!!
Happy to help! 👍️
Paul,
A similar one here....
=IF(ISBLANK([Rebate ROI]1), "0", (((([T1 min dollar]1 * [Tier 1 %]1)  $[Projected Rebate $]1) + (([T1 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((([T1 min dollar]1  $[2019 Spend]1) / $[2019 Spend]1) >= 0.2001, "4.5%", IF((([T1 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((([T1 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.
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", (((([T1 min dollar]1 * [Tier 1 %]1)  $[Projected Rebate $]1) + (([T1 min dollar]1 * (VALUE(SUBSTITUTE([SI Achieved]1, "%", ""))  $[Projected SI $]1)) / [Additional Spend Needed]1))))
Ok. Think about it this way...
We use the SUBSTITUTE to remove the "%".
SUBSTITUTE([SI Achieved]1, "%", "")
Then we take that result and wrap it in the VALUE function to convert it to a number
VALUE(SUBSTITUTE([SI Achieved]1, "%", ""))
Then we divide that by 100 to convert the number to a decimal so it can be factored as if it is a percent.
(VALUE(SUBSTITUTE([SI Achieved]1, "%", "")) / 100)
So any cell references that contain a text of "##%" would be replaced by the above to convert the text that looks like "##%" into a usable decimal.
If your formula is
=IF(ISBLANK([Rebate ROI]1), "0", (((([T1 min dollar]1 * [Tier 1 %]1)  $[Projected Rebate $]1) + (([T1 min dollar]1 * [SI Achieved]1)  $[Projected SI $]1)) / [Additional Spend Needed]1))
Then dropping in the above would be
=IF(ISBLANK([Rebate ROI]1), "0", (((([T1 min dollar]1 * [Tier 1 %]1)  $[Projected Rebate $]1) + (([T1 min dollar]1 * (VALUE(SUBSTITUTE([SI Achieved]1, "%", "")) / 100))  $[Projected SI $]1)) / [Additional Spend Needed]1))
You're the best, thank you for breaking it down so it makes sense. I've only had Smartsheet for 2 days so knowing why everything is needed is super helpful!
Happy to help! 👍️
Paul,
If this is dividing a dollar amount by a dollar amount, and I want a percent out of it, what am I doing wrong? Do I need to add the "%" part somewhere?
=IF([Additional Spend Needed  All]1 = "", "", ([Additional Rebate & SI Gained]1, /[Additional Spend Needed  All]1) * 100)
Thank you!
If you are dividing a number by a number, you wouldn't need to add a "%" anywhere in your formula (depending on your column type). If it is a text/number type column, then you should be able to just format the column as a percentage.
@Tanya Boehmke
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
Paul,
Spot checking the previous formulas you were helping me with and found an issue.....
This formula is in the 1st cell and is returning the correct answer.
=IF([SI Type]59 = "Standard", IF([Projected Growth]59 >= 0.2001, "4.50%", IF([Projected Growth]59 >= 0.1001, "4.00%", "3.50%")), IF([SI Type]59 = "Fastener", IF([Projected Growth]59 >= 0.1, "2.50%", IF([Projected Growth]59 <= 0.1, "1.50%")), IF([SI Type]59 = "MIN", "MIN", [SI Type]59)))
This formula is in the 2nd cell. For all cells that are following the not true portion of the above, returning the [SI Type] 59, the formula below works great. For cells that are following the IF=Standard etc. from the above formula, I am needing to divide the result by 100, not by 1. Is there a way to format this so all cells can be divided by the same amount, either 1 or 100 to get the decimal in the right place?
=IF([Projected SI %]61 = 0, "0", IF([Projected SI %]61 = "MIN", "5000", [Projected Vendor Spend]61 * VALUE(SUBSTITUTE([Projected SI %]61, "%", "")) / 1))
I'm not sure I follow. Why are you dividing by 1?
:) Well originally it was /100, but I noticed the total was coming up incorrect. ($271.87 instead of $27,187) so I was messing with it and changing it to /1 gave me the same total as my excel spreadsheet I transferred everything from.
But then I realized that it wasn't working for those I mentioned above.
Ok. What are the column names that each of the formulas are in?
Basically what we are going to do is replace the 1 by an IF statement that says "IF cell = "this", then output 100, otherwise output 1". This IF will determine whether to divide by 1 or 100 based on your criteria.
The column the IF formula is reading is [Projected SI %], if not true it multiplies [Projected SI %] by [Projected Vendor Spend]
The column we are getting the result in is [Projected SI $]
=IF([Projected SI %]71 = 0, "0", IF([Projected SI %]71 = "MIN", "5000", [Projected Vendor Spend]71 * VALUE(SUBSTITUTE([Projected SI %]71, "%", "")) / 100))
Works great! You are amazing!
Ugh what am I doing wrong on this one??!!! I'm going to really boost your points with these crazy formulas :)
=IF([SI Achieved T3]438 = 0, [Rebate ROI T3]438, IF($[Rebate ROI T3]438 = "", "", (((([T3 min dollar]438 * [Tier 3 %]438)  $[Projected Rebate $]438) + (([T3 min dollar]438 * (VALUE(SUBSTITUTE([SI Achieved T3]438, "%", "")) / IF([SI Type]@row = "Standard", 100, IF([SI Type]@row = "Fastener", 100, 1))  $[Projected SI $]438)) / [Additional Spend Needed T3]438)))))
The SI Type is Standard for my test row, it is giving me 287,697,455% instead of the correct 29.6%
What is in this cell?
$[Projected SI $]438