Including a comma for currency within an existing formula

Options
Miss_Priss
Miss_Priss ✭✭
edited 04/05/24 in Formulas and Functions

There are two columns with formulas that I am looking to incorporate adding not only a dollar sign ($) but also a comma (,) to properly represent US currency.

  • The LVL or Level column represents a current level. Each level has a different threshold requirement that must be met for both Current LO MRR$ and Progress to LO MRR$.
  • The LO MRR$ column is the current total which is referenced in the formula for both Current LO MRR$ and Progress to LO MRR$ columns. The cells in the LO MRR$ column are linked to cells in another sheet and are auto populated per weekly updates.
  • The formulas for Current LO MRR$ and Progress to LO MRR$ columns are very similar in their function. The Current LO MRR$ column compared the LO MRR$ to the threshold of the current LVL, whereas the Progress to LO MRR$ column compares the LO MRR$ to the threshold requirements of the next LVL in order to progress.

    As you can see from the example below the currency from the LO MRR$ column is currently being represented as a straight numerical value with a preceding dollar sign ($) in both the Current LO MRR$ and Progress to LO MRR$ columns.

    I am hoping there is a way to adjust the formula so that what is represented is currency rather than just a numerical response. Example: $12,119 of $1,500

    The formula below is from the Current LO MRR$ column. Is there any adjustments that can be made to accomplish this?

    =IF([LO MRR$]@row <> "", "$" + [LO MRR$]@row + " of " + IF(LVL@row = 1, "$0", IF(LVL@row = 2, "$1,500", IF(LVL@row = 3, "$10,000", IF(LVL@row = 4, "$10,000", IF(LVL@row = 5, "$50,000"))))))

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Not gonna lie... this one made my head hurt a little. Give this one a try and see if it works. I didn't take the time to setup a test sheet, but if this doesn't do it I may have to try that.

    =IF([LO MRR$]@row <> "", "$" + IF([LO MRR$]@row < 1000, [LO MRR$]@row, IF([LO MRR$]@row < 10000, LEFT([LO MRR$]@row, 1) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 100000, LEFT([LO MRR$]@row, 2) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 1000000, LEFT([LO MRR$]@row, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 10000000, LEFT([LO MRR$]@row, 1) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 100000000, LEFT([LO MRR$]@row, 2) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 1000000000, LEFT([LO MRR$]@row, 3) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3)))))))) + " of " + IF(LVL@row = 1, "$1,500", IF(LVL@row = 2, "$10,000", IF(LVL@row = 3, "$50,000", IF(LVL@row = 4, "$50,000", IF(LVL@row = 5, "$50,000"))))), "$0 of " + IF(LVL@row = 1, "$1,500", IF(LVL@row = 2, "$10,000", IF(LVL@row = 3, "$50,000", IF(LVL@row = 4, "$50,000", IF(LVL@row = 5, "$50,000"))))))

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 08/02/23
    Options

    It's a little long, but this should do it. I wasn't sure what kind of finances you may be dealing with... this should work with anything under one billion.

    =IF([LO MRR$]@row <> "", "$" + IF([LO MRR$]@row < 1000, [LO MRR$]@row, IF([LO MRR$]@row < 10000, LEFT([LO MRR$]@row, 1) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 100000, LEFT([LO MRR$]@row, 2) + "," + RIGHT([LO MRR$]@row, 3),   IF([LO MRR$]@row < 1000000, LEFT([LO MRR$]@row, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 10000000, LEFT([LO MRR$]@row, 1) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 100000000, LEFT([LO MRR$]@row, 2) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 1000000000, LEFT([LO MRR$]@row, 3) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3)))))))) + " of " + IF(LVL@row = 1, "$0", IF(LVL@row = 2, "$1,500", IF(LVL@row = 3, "$10,000", IF(LVL@row = 4, "$10,000", IF(LVL@row = 5, "$50,000"))))))

  • Miss_Priss
    Options

    @Carson Penticuff Thank you so much for your feedback! That was a lifesaver. 😁

    Of course I've stumbled across another revision that needs to be made. 😂

    The formula in the Progress to LO MRR$ column is very similar but I've discovered that we need to include a "$0" for the blank responses from the LO MRR$ column. That way we can show there hasn't been any progress towards the goal, which will always be the case and is slightly different than the Current LO MRR$ which at times doesn't have a threshold requirement (at least for LVL 1).

    The current formula below generates a blank cell if there is a blank cell from the LO MRR$ column. I've attempted to make revision to change this but I just can't figure out the correct argument. Does anyone have any suggestions?

    =IF([LO MRR$]@row <> "", "$" + IF([LO MRR$]@row < 1000, [LO MRR$]@row, IF([LO MRR$]@row < 10000, LEFT([LO MRR$]@row, 1) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 100000, LEFT([LO MRR$]@row, 2) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 1000000, LEFT([LO MRR$]@row, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 10000000, LEFT([LO MRR$]@row, 1) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 100000000, LEFT([LO MRR$]@row, 2) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 1000000000, LEFT([LO MRR$]@row, 3) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3)))))))) + " of " + IF(LVL@row = 1, "$1,500", IF(LVL@row = 2, "$10,000", IF(LVL@row = 3, "$50,000", IF(LVL@row = 4, "$50,000", IF(LVL@row = 5, "$50,000"))))))

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Not gonna lie... this one made my head hurt a little. Give this one a try and see if it works. I didn't take the time to setup a test sheet, but if this doesn't do it I may have to try that.

    =IF([LO MRR$]@row <> "", "$" + IF([LO MRR$]@row < 1000, [LO MRR$]@row, IF([LO MRR$]@row < 10000, LEFT([LO MRR$]@row, 1) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 100000, LEFT([LO MRR$]@row, 2) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 1000000, LEFT([LO MRR$]@row, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 10000000, LEFT([LO MRR$]@row, 1) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 100000000, LEFT([LO MRR$]@row, 2) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3), IF([LO MRR$]@row < 1000000000, LEFT([LO MRR$]@row, 3) + "," + MID([LO MRR$]@row, 2, 3) + "," + RIGHT([LO MRR$]@row, 3)))))))) + " of " + IF(LVL@row = 1, "$1,500", IF(LVL@row = 2, "$10,000", IF(LVL@row = 3, "$50,000", IF(LVL@row = 4, "$50,000", IF(LVL@row = 5, "$50,000"))))), "$0 of " + IF(LVL@row = 1, "$1,500", IF(LVL@row = 2, "$10,000", IF(LVL@row = 3, "$50,000", IF(LVL@row = 4, "$50,000", IF(LVL@row = 5, "$50,000"))))))

  • Miss_Priss
    Options

    @Carson Penticuff You are amazing! That worked perfectly! I'm so grateful for your help. Thank you 😀

  • Miss_Priss
    Options

    @Carson Penticuff QQ for you.

    I made a slight revision to the LO MRR$ column which is a VLOOKUP. Now it includes a response of "$0" if there is an error and the VLOOKUP is not found on the other sheet.

    By adding the "$" into the response for this field I'm getting two "$" in any response which the LO MRR$ is "$0". Is there any way to exclude the extra "$" for LO MRR$ responses = $0 but continue to include the "$" for all other responses?

    This is what it looks like with the change:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!