Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formating % in concatenate formula
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! -- Outapin ------------------------------------------------------------------------ Outapin's Profile: http://www.excelforum.com/member.php...o&userid=29278 View this thread: http://www.excelforum.com/showthread...hreadid=489964 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formating % in concatenate formula
use the Round function: =Round(argument,# of decimals). -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=489964 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formating % in concatenate formula
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 -- Regards, Peo Sjoblom "Outapin" wrote in message ... 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! -- Outapin ------------------------------------------------------------------------ Outapin's Profile: http://www.excelforum.com/member.php...o&userid=29278 View this thread: http://www.excelforum.com/showthread...hreadid=489964 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formating % in concatenate formula
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. -- Outapin ------------------------------------------------------------------------ Outapin's Profile: http://www.excelforum.com/member.php...o&userid=29278 View this thread: http://www.excelforum.com/showthread...hreadid=489964 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formating % in concatenate formula
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. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=489964 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formating % in concatenate formula
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 ! -- Outapin ------------------------------------------------------------------------ Outapin's Profile: http://www.excelforum.com/member.php...o&userid=29278 View this thread: http://www.excelforum.com/showthread...hreadid=489964 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formating % in concatenate formula
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),"%)"," have been employed for more than 2 years, compared with ",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) -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=489964 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formating % in concatenate formula
YOU'RE MY HERO !!!!!!!! THANK YOU SO MUCH !!!!! It works like a charm !!! -- Outapin ------------------------------------------------------------------------ Outapin's Profile: http://www.excelforum.com/member.php...o&userid=29278 View this thread: http://www.excelforum.com/showthread...hreadid=489964 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide formula | Excel Worksheet Functions | |||
Conditional Formating for Formula Cells | Excel Discussion (Misc queries) | |||
Strange Formating problem with text and formula in same cell | Excel Worksheet Functions | |||
CONCATENATE formula | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |