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.

=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...

Using VALUE within an IF formula to covert text to number

Options

Answers

Paul Newcome ✭✭✭✭✭✭

02/27/20
Options
That's great! Glad it's working for you now. 👍️

CERTIFIED SMARTSHEET PLATINUM PARTNER
10xViz.com
0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Return the max date looking at multiple criteria
Trying to add the most recent training completion date (column is date/non-restricted) using mulitple criteria, to include: email address, training type ("refresher", or "intensive") and status ("attended") to a designated column (date/non-restricted) on another sheet. The formulas below are pulling the same date into each…
Dynamic year formula for the previous year
Hello! I would like to show the average score for the previous year without indicating specific year (instead of showing 2023 I use formula (YEAR(TODAY()) – 1. =AVG(COLLECT({Vendor Evaluation Sheet - Archive - Score}, {Vendor Evaluation Sheet - Task ID}, [Task ID]@row, {Vendor Evaluation Sheet - Archive - Created},…
How to Incorporate OR with a COLLECT Formula
I have the following working formula that I need to modify to look for either two people in the "Resi - Onboarding Specialist" column in addition to meeting the date range. The formula below is working for one application but the next application needs to also include calculating the median if the "Resi - Onboarding…

Using VALUE within an IF formula to covert text to number

Answers

Help Article Resources

Categories