Including a comma for currency within an existing formula

Miss_Priss

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

  

Carson Penticuff

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

Carson Penticuff

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

@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

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

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

Miss_Priss

@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: