Rounding up on Concatenate formulas

[MelaniePPC]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The main thing I'm trying to accomplish is to have a cell to formulate my ROI (or ROAS – return on ad spend) as a ratio (ex: 7:1). From some research I did I saw that this can be down be creating a concatenate formula. So I have a cell which firsts calculates ROAS (C5) and have also created another cell that simply has the number one (C6). I've then created the formula of =CONCATENATE(C5,":",C6) which does exactly what I want it to do – presents it as a ratio.

However, it doesn't round C5 to a whole number (even if it's rounded in C5) but rather it included it as number with 14 numbers after the decimal point (7.05402198619073:1) how do I get it so simply say 7:1 using a formula?

 

[Bob Greenblatt]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel The
main thing I'm trying to accomplish is to have a cell to formulate my ROI (or
ROAS – return on ad spend) as a ratio (ex: 7:1). From some research I did I
saw that this can be down be creating a concatenate formula. So I have a cell
which firsts calculates ROAS (C5) and have also created another cell that
simply has the number one (C6). I've then created the formula of
=CONCATENATE(C5,":",C6) which does exactly what I want it to do – presents it
as a ratio.

However, it doesn't round C5 to a whole number (even if it's rounded in C5)
but rather it included it as number with 14 numbers after the decimal point
(7.05402198619073:1) how do I get it so simply say 7:1 using a formula?

Excel calculates all internal values to 15 significant digits. The cell
format controls how that is displayed in the cell. To do what you want, you
need to create a text string (what concatenate does). An easy formula to do
this is:
=text(c5,²0²)&²:²&c6

The ampersand symbol does the same thing as concatenate.

 

[Carl Witthoft]

Do you mean:

CONCATENATE(ROUND(C5,0),":",C6)

?

But if your cell C6 is always one, you are not generating a ratio but
simply a way to represent the value in C5.

So, instead, take whatever formula you have in C5, (I'll call it
ROAS(B5) here) and enter into cell C5 the formula

=ROUND(ROAS(B5),0)

Then select C5 , or column C, and under the menu item Format Cell,
select Number, select Custom, and type 0":1" (or something close to
that). The cell will CONTAIN the value you want, and DISPLAY it in the
format you want.

Carl