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

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