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
-
=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.
-
=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.
=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
✅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!
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.
-
=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?
-
=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.
-
That worked! I cannot thank you enough Paul!!!!!
-
Happy to help! 👍️
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!