appearance of a value arrived at by using CONCATENATE and ROUND

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Format the cells to align right:

=CONCATENATE("$",TEXT(ROUND('G:\AOMBudget\Daily RFSR\DAILY RFSR
FY06.xls'!auth.1fm/1000000,1),"#.0"))&"M"

Biff
 
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,
Thanks again. Nothin' like a man with brains. If I wasn't married I'd
consider looking you up!

jan
 
Back
Top