Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Shirley
 
Posts: n/a
Default show text after formulas

I have some formulas with text added to the result. For example, I might
have a result of: 4.9¢/kWh
In the formula cell, I set the numeric format to number, one digit after
decimal.

How do I retain the format when I have the numeric set to show commas? When
I want to show a result of: 2,345,899 kWh Excel gives me a result of:
2345899 kWH.

Why is it ignoring my format when I introduce text? A formula example when
this happens might be:

=ROUND(((G40/H45)*D19*100 ),0)&"kWh"

help! this is driving me crazy!! I want to show units in the same cell, but
these numbers need commas to read them easier!!

Shirley
  #2   Report Post  
Sloth
 
Posts: n/a
Default

I usually make custom formats that include the units I want. For example the
cell would contain;

=ROUND(((G40/H45)*D19*100 ),0)

and the custom format is;

#,### "kwh"

hope this helps.
  #3   Report Post  
Paul Hightower
 
Posts: n/a
Default

Shirley:
You are mixing a numerical result with text. the use of the "&" tells excel
the cell contents is text. That is what is kiliing your formatting of the
ROUND() function. I suspect if you surround the ROUND() function with a
TEXT() convserion you can make the format behave.
Paul

"Shirley" wrote:

I have some formulas with text added to the result. For example, I might
have a result of: 4.9¢/kWh
In the formula cell, I set the numeric format to number, one digit after
decimal.

How do I retain the format when I have the numeric set to show commas? When
I want to show a result of: 2,345,899 kWh Excel gives me a result of:
2345899 kWH.

Why is it ignoring my format when I introduce text? A formula example when
this happens might be:

=ROUND(((G40/H45)*D19*100 ),0)&"kWh"

help! this is driving me crazy!! I want to show units in the same cell, but
these numbers need commas to read them easier!!

Shirley

  #4   Report Post  
Dave Breitenbach
 
Posts: n/a
Default

try this...
=TEXT(ROUND(((g40/h45)*d19*100 ),0),"#,##")&" kWh"


"Paul Hightower" wrote:

Shirley:
You are mixing a numerical result with text. the use of the "&" tells excel
the cell contents is text. That is what is kiliing your formatting of the
ROUND() function. I suspect if you surround the ROUND() function with a
TEXT() convserion you can make the format behave.
Paul

"Shirley" wrote:

I have some formulas with text added to the result. For example, I might
have a result of: 4.9¢/kWh
In the formula cell, I set the numeric format to number, one digit after
decimal.

How do I retain the format when I have the numeric set to show commas? When
I want to show a result of: 2,345,899 kWh Excel gives me a result of:
2345899 kWH.

Why is it ignoring my format when I introduce text? A formula example when
this happens might be:

=ROUND(((G40/H45)*D19*100 ),0)&"kWh"

help! this is driving me crazy!! I want to show units in the same cell, but
these numbers need commas to read them easier!!

Shirley

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel formulas to create large blocks of text Greg Boettcher Excel Discussion (Misc queries) 5 June 12th 05 06:41 PM
Sort or Filter option? Mcobra41 Excel Worksheet Functions 3 February 23rd 05 08:22 PM
Text to Columns - moves text up Stoofer Excel Discussion (Misc queries) 2 February 19th 05 11:04 PM
How do a convert a "text only (no formulas)" Excel (.xls) file to. Steve Excel Discussion (Misc queries) 1 February 2nd 05 06:55 PM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM


All times are GMT +1. The time now is 12:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"