| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
appearance of a value arrived at by using CONCATENATE and ROUN
LOL!
You're welcome! Biff "Jan Buckley" wrote in message ... Biff, Thanks again. Nothin' like a man with brains. If I wasn't married I'd consider looking you up! jan "Biff" wrote: See this screencap: http://img149.imageshack.us/img149/339/aligndatalb8.jpg Notice the FONT. I used a fixed width font to get everything exactly aligned. Here's the formula: ="$"&REPT(" ",5-LEN(TEXT(ROUND(A1/1000000,1),"#.0")))&TEXT(ROUND(A1/1000000,1),"#.0")&"M" Just add your path. (I hate long paths!) <g Biff "Jan Buckley" wrote in message ... Biff, Today I have a similar problem. I'm usiing the same formulas, but now I want the dollar signs to line up to the left of the values. My figures are all converted to millions (you know how it is in the government!) and the figure will never get larger than three digits, i.e. 500M, but it could be as small as .90M. I need those dollar signs all lined up in a pretty little column. Help! Jan "Biff" wrote: You're welcome. Thanks for the feedback! Biff "Jan Buckley" wrote in message ... A formula that works is indeed a thing of beauty!! Thanks, Biff. "Biff" wrote: Format the cells to align right: =CONCATENATE("$",TEXT(ROUND('G:\AOMBudget\Daily RFSR\DAILY RFSR FY06.xls'!auth.1fm/1000000,1),"#.0"))&"M" Biff "Jan Buckley" wrote in message ... I'm using the following formula on a spredsheet: CONCATENATE("$",ROUND('G:\AOMBudget\Daily RFSR\DAILY RFSR FY06.xls'!auth.1fm/1000000,1))&"M" A variation of this formula is used on other rows and I would the amounts to line up in a column and all have one decimal, even if it is zero. For instance, the result of the above formula when rounded to 1 digit is $46M; rounded to 2 digits it is $45.97M, etc. I want it to say $46.0M. I guess what I'm trying to say is, I want all the numbers to line up to the left of the decimal and have only one number to the right of the decimal, even if it is zero. Can anyone make it happen? Thanks. |
Threaded Mode
