Excel Help - How to Concatenate without all the extra decimals??

May 19, 2017 8:13 AM

I'm working on a sheet where I have a summary box explaining the data in the spread sheet. Something like "With 80 guests per day and and average transaction size of $14.32, you can expect 61% margins. Concatenation is not working the way I hoped =/

For arguments sake, "80 is referencing A1, $14.32 is referencing B1, and 61% is referencing C1

The concatenation formula works and is pulling the correct number. However, I have the cells set to decrease the decimal so it is showing whole numbers, even though the true value of A1 is 79.98431

What I need is the accuracy of the decimals, but the formula to return the value "80" in the concatenation formula so it doesn't say "With 79.98431 guests per day and average transaction size of $14.32034178, you can expect margins of 61.32%.

Any thoughts? thanks!

posted by FireStyle to Computers & Internet (5 answers total)

=CONCATENATE("With", ROUND(A1,0), "guests per day and an average transaction size of", ROUND(B1,2), ", you can expect margins of ", ROUND(C1,0))
posted by adamrice at 8:18 AM on May 19, 2017 [3 favorites]

The rounding you're doing in the cells is a matter of formatting - you're not actually changing the value, so when you concatenate, it uses the original value. Maybe try using ROUND(number, num_digits) on the values in your formula?
posted by zamboni at 8:18 AM on May 19, 2017

Best answer: You want the TEXT function, which allows you to represent a value as text in a given format.

="With "&TEXT(A1,"0")&" guests per day and average transaction size of "&TEXT(B1,"$0.00")&", you can expect margins of "&TEXT(C1,"0%")&"."
posted by DevilsAdvocate at 8:19 AM on May 19, 2017

Ha, beat to it. ROUND(number, num_digits) works by asking for your reference point, then the number of digits you'd like to round the variable to, splitting at .49 repeating & .5. In fact, in your use case, you might be well-served to use a RoundUp() formula for your guests, as it operates in the same way as a Round() does, but does this sort of behavior to roll up to the next integer:

> whereas 79 guests precise – ROUNDUP(79.0,0) – will "round up" to 79,
> 79.1 guests – ROUNDUP(79.1,0) – will round straight up to 80.

And, this may be common sense, but be advised that you can of course nest this formula in place of any variable.
For example, if you reference cell A1, it's valid if you replace =CONCATENATE(A1,...) with CONCATENATE(ROUND(A1,0),...).
posted by a good beginning at 8:30 AM on May 19, 2017

Response by poster: @DevilsAdvocate - that did the trick! @ a good beginning, adamrice, and zamboni - that's how I had it before, but the rounding was creating errors that became exponentially worse as the input values increased.

Thanks for the help!
posted by FireStyle at 8:58 AM on May 19, 2017 [1 favorite]