Using VALUE within an IF formula to covert text to number

24

Answers

  • Paul Newcome
    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
    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!

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

    @Tanya Boehmke

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best, 

    Andrée

    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.

  • Tanya Boehmke
    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
    Paul Newcome ✭✭✭✭✭✭

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

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


  • Tanya Boehmke
    Tanya Boehmke ✭✭✭✭✭

    Works great! You are amazing!

  • Tanya Boehmke
    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
    Paul Newcome ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!