# Including a comma for currency within an existing formula

Options
edited 04/05/24

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

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

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

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

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

• Options

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

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