Using VALUE within an IF formula to covert text to number

Paul Newcome

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", (((([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))

Then dropping in the above would be

=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, "%", "")) / 100)) - $[Projected SI $]1)) / [Additional Spend Needed]1))

Tanya Boehmke

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!

Paul Newcome

Happy to help! 👍️

Tanya Boehmke

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!

Paul Newcome

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.

Andrée Starå

@Tanya Boehmke

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée

Tanya Boehmke

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

Paul Newcome

I'm not sure I follow. Why are you dividing by 1?

Tanya Boehmke

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

Paul Newcome

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.

Tanya Boehmke

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

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

Tanya Boehmke

Works great! You are amazing!

Tanya Boehmke

Ugh what am I doing wrong on this one??!!! I'm going to really boost your points with these crazy formulas :)

=IF([SI Achieved T-3]438 = 0, [Rebate ROI T-3]438, IF($[Rebate ROI T-3]438 = "-", "-", (((([T-3 min dollar]438 * [Tier 3 %]438) - $[Projected Rebate $]438) + (([T-3 min dollar]438 * (VALUE(SUBSTITUTE([SI Achieved T-3]438, "%", "")) / IF([SI Type]@row = "Standard", 100, IF([SI Type]@row = "Fastener", 100, 1)) - $[Projected SI $]438)) / [Additional Spend Needed T-3]438)))))

The SI Type is Standard for my test row, it is giving me -287,697,455% instead of the correct 29.6%

Paul Newcome

What is in this cell?

$[Projected SI $]438