Using VALUE within an IF formula to covert text to number

Tanya Boehmke

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.

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

Andrée Starå

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!

Paul Newcome

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

Tanya Boehmke

This gives me an Invalid Value error?

Tanya Boehmke

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

The suggestion from Andree gives me an Invalid Operation error.

Any ideas?

Paul Newcome

What is in [Projected SI %]1?

Tanya Boehmke

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?

Paul Newcome

What is that formula?

Tanya Boehmke

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

Paul Newcome

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)

Tanya Boehmke

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

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.

Tanya Boehmke

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

Paul Newcome

Happy to help! 👍️

Tanya Boehmke

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.

Tanya Boehmke

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