Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 48
Default how to change number to text with specific format in function

=IF(C3<0, (C3-C2)/B2*100 & " (" & C3-C2 & ")","")

This is the function I used at this moment. but it has some problem. all
data in column B & C are number format with no more than 4 decimal digits, so
that both (C3-C2)/B2*100 and C3-C2 are still number, especially without
decimal digits control.

The result I expected is for example when C3 = 2.5350 and C2 = 2.5060 and B2
= 2.456, the result shows me "1.18 (0.0290)". 2 decimal digits in the first
part, 4 decimal digits in the second part. Besides, it is possible that C3 is
smaller than C2, so that the negative number is also expected if it happens.

Who can help me to realize this result?

Thanks go to anyone who think about my troubles!

Best Regards,
Viesta Wu
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,080
Default how to change number to text with specific format in function

What is the result that you are getting and what are you expecting?
__________________________________________________ ______________________

"ViestaWu" wrote in message
...
=IF(C3<0, (C3-C2)/B2*100 & " (" & C3-C2 & ")","")

This is the function I used at this moment. but it has some problem. all
data in column B & C are number format with no more than 4 decimal digits,
so
that both (C3-C2)/B2*100 and C3-C2 are still number, especially without
decimal digits control.

The result I expected is for example when C3 = 2.5350 and C2 = 2.5060 and
B2
= 2.456, the result shows me "1.18 (0.0290)". 2 decimal digits in the
first
part, 4 decimal digits in the second part. Besides, it is possible that C3
is
smaller than C2, so that the negative number is also expected if it
happens.

Who can help me to realize this result?

Thanks go to anyone who think about my troubles!

Best Regards,
Viesta Wu



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 48
Default how to change number to text with specific format in function

the result I get now is 1.18078175895767 (0.0290000000000004). the result I
expected is 1.18 (0.0290).


"Vasant Nanavati" wrote:

What is the result that you are getting and what are you expecting?
__________________________________________________ ______________________

"ViestaWu" wrote in message
...
=IF(C3<0, (C3-C2)/B2*100 & " (" & C3-C2 & ")","")

This is the function I used at this moment. but it has some problem. all
data in column B & C are number format with no more than 4 decimal digits,
so
that both (C3-C2)/B2*100 and C3-C2 are still number, especially without
decimal digits control.

The result I expected is for example when C3 = 2.5350 and C2 = 2.5060 and
B2
= 2.456, the result shows me "1.18 (0.0290)". 2 decimal digits in the
first
part, 4 decimal digits in the second part. Besides, it is possible that C3
is
smaller than C2, so that the negative number is also expected if it
happens.

Who can help me to realize this result?

Thanks go to anyone who think about my troubles!

Best Regards,
Viesta Wu




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,080
Default how to change number to text with specific format in function

Try (untested):

=IF(C3<0, TEXT((C3-C2)/B2*100,"0.00") & " (" & TEXT(C3-C2,"0.0000" &
")","")
__________________________________________________ _________________________


"ViestaWu" wrote in message
...
the result I get now is 1.18078175895767 (0.0290000000000004). the result
I
expected is 1.18 (0.0290).


"Vasant Nanavati" wrote:

What is the result that you are getting and what are you expecting?
__________________________________________________ ______________________

"ViestaWu" wrote in message
...
=IF(C3<0, (C3-C2)/B2*100 & " (" & C3-C2 & ")","")

This is the function I used at this moment. but it has some problem.
all
data in column B & C are number format with no more than 4 decimal
digits,
so
that both (C3-C2)/B2*100 and C3-C2 are still number, especially without
decimal digits control.

The result I expected is for example when C3 = 2.5350 and C2 = 2.5060
and
B2
= 2.456, the result shows me "1.18 (0.0290)". 2 decimal digits in the
first
part, 4 decimal digits in the second part. Besides, it is possible that
C3
is
smaller than C2, so that the negative number is also expected if it
happens.

Who can help me to realize this result?

Thanks go to anyone who think about my troubles!

Best Regards,
Viesta Wu






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default how to change number to text with specific format in function

=IF(C3<0,TEXT((C3-C2)/B2*100,"0.00 (")&TEXT(C3-C2,"0.0000)"),"")


"ViestaWu" wrote:

=IF(C3<0, (C3-C2)/B2*100 & " (" & C3-C2 & ")","")

This is the function I used at this moment. but it has some problem. all
data in column B & C are number format with no more than 4 decimal digits, so
that both (C3-C2)/B2*100 and C3-C2 are still number, especially without
decimal digits control.

The result I expected is for example when C3 = 2.5350 and C2 = 2.5060 and B2
= 2.456, the result shows me "1.18 (0.0290)". 2 decimal digits in the first
part, 4 decimal digits in the second part. Besides, it is possible that C3 is
smaller than C2, so that the negative number is also expected if it happens.

Who can help me to realize this result?

Thanks go to anyone who think about my troubles!

Best Regards,
Viesta Wu

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
Change number format from text to number? Scot New Users to Excel 2 December 1st 05 05:15 PM
Change number (in text format) to numeric format Pam Excel Discussion (Misc queries) 5 October 24th 05 07:45 PM
Hoe to change text format .126 to number format 0.126 vitality Excel Discussion (Misc queries) 3 October 6th 05 01:31 PM
How to change text format .126 to number format 0.126 ? vitality Excel Worksheet Functions 2 October 6th 05 01:02 PM
How do i change numbers in text format to number format? Greg New Users to Excel 1 December 14th 04 06:22 PM


All times are GMT +1. The time now is 10:27 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"