How to display exact value when adding text to cell

Mike TV

I've got a sheet with a dollar value in one column such as $20.70. I'm trying to format another column so that it says "$20.70 Per Hour" in it using a formula such as ="$"+Rate@row+" Per Hour". However, the results actually display as "$20.7 Per Hour".

Additionally, I've got other columns where the column is set to a certain number of decimal places. When I try to call upon that value from another cell it displays the unrounded value. Since I'm adding text in the new cell, I can't set that to a certain # of decimal places in the column settings because it is ignored.

How can I get SmartSheet to reference exactly what is in a cell without displaying something different?

Toufong Vang

I see... ="$" + INT(Rate@row) + "." + ((ROUND(Rate@row - INT(Rate@row), 2)) * 100) + " Per Hour" will work.

Take the integer of the rate and subtract it from the rate. This leaves you the remaining decimal values which you then round to 2 decimal places. Multiply that by 100 to raise this to the tens place (get a whole number).

Now assemble all the pieces... "$" + the Integer of the rate + "." + the rounded decimal value raised to the tens place + " Per Hour"

Mike TV

For the 2nd problem, I've tried ROUNDUP and ROUNDDOWN but that causes additional problems when factoring in a per unit cost. Even rounding up by 1 penny messes up a per unit cost which is extremely important not to round.

Mike TV

Figured out the 2nd problem. I want to use ROUND and not the UP or DOWN version. The first problem is still unresolved. :(

Toufong Vang

The formula, = "$" + ROUND(Rate@row,2) + " Per Hour" , rounds the rate to 2 decimal places before prepending "$" to it and appending " Per Hour" after it. So a rate of 23 2/3 dollars per hour will work out to "$23.67 Per Hour".

Mike TV

@Toufong Vang

Thanks. I figured out that part of it as I indicated above. I was using ROUNDUP or ROUNDDOWN instead of just ROUND.

Now my problem is how to get $20.70 to display as "$20.70 Per Hour" instead of "$20.7 Per Hour". I'm not sure that it's possible but would love a work around if anyone can think of one.

Toufong Vang

I see... ="$" + INT(Rate@row) + "." + ((ROUND(Rate@row - INT(Rate@row), 2)) * 100) + " Per Hour" will work.

Take the integer of the rate and subtract it from the rate. This leaves you the remaining decimal values which you then round to 2 decimal places. Multiply that by 100 to raise this to the tens place (get a whole number).

Now assemble all the pieces... "$" + the Integer of the rate + "." + the rounded decimal value raised to the tens place + " Per Hour"

Mike TV

@Toufong Vang

You're a genius! I'm not going to pretend I understand what it's doing but it worked like a charm. Thanks man I appreciate it. Will make my end result look more professional.

Jeff Reisman

@Mike TV

Toufong's solution is undoubtedly easier and more elegant than mine.

The one place it falls short is when you have a rate value that has no decimal places, like $20.00. Toufong's solution leaves you with "$20.0 Per Unit".

I worked on this for a while and kept getting interrupted, so I was beaten to the punch - but I wanted to post it anyhow.

If your rate is entered manually and not being used in calculations, just enter an apostrophe before typing $20.70. That makes it a text value, which can be concatenated to other text easily and exactly as entered. (I see already this isn't your situation.)

If your dollar amount value is a number, in a column formatted for currency, this is a little more difficult. But hang with me, we can do this. In this case, the dollar sign and extra decimal place aren't really there. The currency formatting puts them there in the presentation layer, but underneath you just have a value of 20.7. (No doubt you are aware of that, but I'm stating it here for others' benefit.) You are on the right track with your formula. Now we need a way to figure out if there's a decimal and only one decimal place...

The first thing is to add a hidden HelperColumn to convert your Rate value to text, and we need to add a space and a couple zeros for everything to work right. So we're going to add a blank space and two zeros to Rate@row.

=Rate@row +" 00"

In your example this gives us a cell containing a text value of 20.7 00 We will use this text value to evaluate and create the Price per unit value. This next bit is a little complicated!

=IF(FIND(".", HelperColumn@row) = 0, ("$" + Rate@row + ".00 Per Unit"), IF(MID(HelperColumn@row, (FIND(".", HelperColumn@row) + 2), 1) = " ", ("$" + Rate@row + "0 Per Unit"), ("$" + Rate@row + " Per Unit")))

In English:

If you don't find a period (decimal marker) in the Helper Column value, add the dollar sign to the Rate value and the text ".00 Per Unit"; otherwise, if there is a blank space two characters after the decimal (indicating a single decimal place,) add the dollar sign to the Rate value and the text "0 Per Unit"; otherwise, add the dollar sign to the Rate value and the text " Per Unit".

The result of the above:

For a Rate@row value of $20.00, HelperColumn shows "20 00", Price Per Unit value is "$20.00 Per Unit".

For a Rate@row value of $20.70, HelperColumn shows "20.7 00", Price Per Unit value is "$20.70 Per Unit".

For a Rate@row value of $20.75, HelperColumn shows "20.75 00", Price Per Unit value is "$20.75 Per Unit".

Note: If you're going to have prices out to three or more decimals places, and want to round these to 2 decimal places for display in the price per unit column, you'll want to replace the last value in the IF formula with ("$" + ROUND(Rate@row, 2) + " Per Unit")

Toufong Vang

Here's how the last formula breaks down...