Help with formating % in concatenate formula

[Outapin]

Hi,

I have created a formula with "concatenate", that includes string and
other formulas in it.

=CONCATENATE("*Of these, ",VLOOKUP($A$2,Terms.xls!$A$6:$D$23,3),"
(",(VLOOKUP($A$2,Terms.xls!$A$6:$D$23,3)/C12)*100,"%)"," have been
employed for more than 2 years, compared with
",VLOOKUP("total",Terms.xls!$A$6:$D$23,3),"
(",(VLOOKUP("total",Terms.xls!$A$6:$D$23,3)/E12)*100,"%),"," at the
department.")

The problem is that the % I get gives me for example : 20.7860052364%.
How do I format my % so that I get only "20%" or "20.8%".

thanks!

 

[Vito]

use the Round function:

=Round(argument,# of decimals)

 

[Peo Sjoblom]

Remove the *100 and wrap it in the TEXT function like in

TEXT(VLOOKUP($A$2,Terms.xls!$A$6:$D$23,3)/C12),"0.00%")

will give you percentage with 2 decimals

 

[Outapin]

Thank you Peo and Vito, but it does not work... I don't know where I go
wrong..
I am fairly new using formulas in Excel..
Peo:
do I have to write "TEXT" somewhere ? Or does it refer to my
concatenate formula?
Do I need to change anything else in my formula or just that part where
I *100 ?

Vito:
Where do I add or write this =Round(argument,# of decimals).
I imagine I have to put someting else instead of "argument", but what ?
My whole formula?

I'm sorry to be asking all these questions, please don't laugh at
me...I am still learning....
Thanks again,
and If anyone else has any ideas of how I can solve this... please let
me know.

 

[Vito]

Nobody's laughing. We all started somewhere and with time we all will
be better. Everyone here is learning every day. That's what makes
these forums so valuable. Even the people that consider themselves
experts or guru's, I am quite sure, learn something these forums.


This would be my version with the Round Function added:

=ROUND(CONCATENATE("*Of these, ",VLOOKUP($A$2,Terms.xls!$A$6:$D$23,3),"
(",(VLOOKUP($A$2,Terms.xls!$A$6:$D$23,3)/C12)*100,"%)"," have been
employed for more than 2 years, compared with
",VLOOKUP("total",Terms.xls!$A$6:$D$23,3),"
(",(VLOOKUP("total",Terms.xls!$A$6:$D$23,3)/E12)*100,"%),"," at the
department."),0)

But, I did note that you seem to be referencing another workbook. Is
that so, or do you want to reference another sheet within the same
workbook. In other words, is Terms.xls another workbook or is Terms
another sheet in your current workbook.

If it is another sheet then, delete the .xls from Term.xls at all
occurrences:

=ROUND(CONCATENATE("*Of these, ",VLOOKUP($A$2,Terms!$A$6:$D$23,3),"
(",(VLOOKUP($A$2,Terms!$A$6:$D$23,3)/C12)*100,"%)"," have been employed
for more than 2 years, compared with
",VLOOKUP("total",Terms!$A$6:$D$23,3),"
(",(VLOOKUP("total",Terms!$A$6:$D$23,3)/E12)*100,"%),"," at the
department."),0)

If it is another workbook, then put square brackets around the workbook
name, and you will still need to add a sheet name after that, for
example: (Note: you will need to replace Sheet1 with the actual sheet
tab name in the Terms book)

=ROUND(CONCATENATE("*Of these,
",VLOOKUP($A$2,[Terms.xls]Sheet1!$A$6:$D$23,3),"
(",(VLOOKUP($A$2,[Terms.xls]Sheet1!$A$6:$D$23,3)/C12)*100,"%)"," have
been employed for more than 2 years, compared with
",VLOOKUP("total",[Terms.xls]Sheet1!$A$6:$D$23,3),"
(",(VLOOKUP("total",[Terms.xls]Sheet1!$A$6:$D$23,3)/E12)*100,"%),"," at
the department."),0)

By the way, if you want 1 decimal place, just replace the last 0 in the
formula with a 1.

Excel has good examples of all its functions in the help files. This
is a good place to start for basic functionality of the functions. The
forum will help with using those basic functions to do some truly
extraordinary things.


Let me know and good luck.

 

[Outapin]

Hi Vito,

Thanks, but it still does not work. I get a #VALUE! message.

Maybe I have to put the "round" function just before my VLOOKUP where I
do my division? Because it's only the % that I want to be rounded, not
the whole thing.
To give you an idea, at the end, my result is:

*Of these, 34 (20.7317073170732%) have been employed for more than 2
years, compared with 298 (30.2845528455285%), at the department.

So, I only my % needs to be rounded up.

and yes, I do make reference to another workbook but when the workbook
is open, Excel automatically take off the brackets and put just the
name of the sheet. It seems to work fine everywhere else.

Thanks again !

 

[Vito]

Sorry,

Try:

=ROUND(CONCATENATE("*Of these
",ROUND(VLOOKUP($A$2,Terms.xls!$A$6:$D$23,3),0),
(",ROUND((VLOOKUP($A$2,Terms.xls!$A$6:$D$23,3)/C12)*100,0),"%)"," hav
been employed for more than 2 years, compared wit
",ROUND(VLOOKUP("total",Terms.xls!$A$6:$D$23,3),0),
(",ROUND((VLOOKUP("total",Terms.xls!$A$6:$D$23,3)/E12)*100,0),"%),",
at the department."),0

 

[Outapin]

YOU'RE MY HERO !!!!!!!!

THANK YOU SO MUCH !!!!!

It works like a charm !!!