the =Text() function diplays numbers in different formats.
replace your Round() function with:
text(round(sum(a2:a4),0),"#,###")
=CONCATENATE("FY09-Q4",CHAR(10)," (",TEXT(ROUND(SUM(A2:A4),0),"#,###"),")")
curiously enough,
you SHOULD be able to use:
="FY09-Q4" & CHAR(10) & " (" & TEXT(ROUND(SUM(A2:A4),0),"#,###") & ")"
but for some reason, it doesn't "evaluate" the char(10) properly...
interesting...
Paul
________________________________
From: sharath sambrani <sharath.c.sambr...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Tue, June 8, 2010 7:22:00 AM
Subject: $$Excel-Macros$$ Need help on CONCATENATE and SUM function
Hi,
i am trying to use sum function in concatenate function. i want to
concatenate 'text' + 'sum of a range' + 'text'
i want to use this in a column heading, say cell A1.
cells A2, A3 and A4 has number values.
in cell i want to display as:
FY09-Q4
(4,464)
FY10 should be displayed in the first line and (4,464) which is the
sum of A2:A4. however when i use the following formula:
=CONCATENATE("FY09-Q4",CHAR(10)," (",ROUND(SUM(A2:A4),0),")")
it displays as follows:
FY09-Q4
(4464)
i need the comma after 1000th digit to be displayed.
does anyone have idea how to deal with this?
thank you in advance.
sharath
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
