| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change number format from text to number? | New Users to Excel | |||
| Change number (in text format) to numeric format | Excel Discussion (Misc queries) | |||
| Hoe to change text format .126 to number format 0.126 | Excel Discussion (Misc queries) | |||
| How to change text format .126 to number format 0.126 ? | Excel Worksheet Functions | |||
| How do i change numbers in text format to number format? | New Users to Excel | |||
Hybrid Mode
