Using VALUE within an IF formula to covert text to number

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

Previous1

Answers

  • Andrée Starå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!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul 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 BoehmkeTanya Boehmke ✭✭✭✭✭

    This gives me an Invalid Value error?

  • Tanya BoehmkeTanya 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 NewcomePaul Newcome ✭✭✭✭✭

    What is in [Projected SI %]1?

  • Tanya BoehmkeTanya 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 NewcomePaul Newcome ✭✭✭✭✭

    What is that formula?

  • Tanya BoehmkeTanya 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 NewcomePaul Newcome ✭✭✭✭✭
    edited 01/24/20

    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 BoehmkeTanya Boehmke ✭✭✭✭✭

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

  • Tanya BoehmkeTanya Boehmke ✭✭✭✭✭

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

  • Tanya BoehmkeTanya 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 BoehmkeTanya 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))))

  • Paul NewcomePaul 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 BoehmkeTanya 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 NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

  • Tanya BoehmkeTanya 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 NewcomePaul 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åAndrée Starå ✭✭✭✭✭

    @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

  • Tanya BoehmkeTanya 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 NewcomePaul Newcome ✭✭✭✭✭

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

  • Tanya BoehmkeTanya 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 NewcomePaul 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 BoehmkeTanya 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))


  • Tanya BoehmkeTanya Boehmke ✭✭✭✭✭

    Works great! You are amazing!

  • Tanya BoehmkeTanya 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 NewcomePaul Newcome ✭✭✭✭✭

    What is in this cell?


    $[Projected SI $]438

Sign In or Register to comment.