| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula text and decimal place
Hi,
Trevor Shuttleworth kindly wrote this formula for me: A1=300 A2=g 300 This weight 300 is in g 300.1 This weight 300.1 is in g 300.12 This weight 300.12 is in g 300.123 This weight 300.123 is in g 300.1234 This weight 300.1234 is in g Using: ="This weight "&INT(A1)&IF(ISERROR(FIND(".",A1)),"","."&MID(A1,F IND(".",A1)+1,LEN(A1)-FIN**D(".",A1)))&" is in "&A2 But, I have 2 areas I would like to refine it. 1 If I use a negative number in A1, I get 1 added to the number like this: -0.5 becomes -1.5 and I dont want that 2 I need this to work when using zeroes as trailing numbers, not always fractional decimal places: 300.00 instead of 300.12 currently it sees 300.00 as just 300. Can anyone help with this? Thanks in advance, Aaron. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula text and decimal place
I am not clear as to why Trevor's formula is so complex, as this seems to do
it ="This weight "&A1&" is in "&A2 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Aaron" wrote in message ups.com... Hi, Trevor Shuttleworth kindly wrote this formula for me: A1=300 A2=g 300 This weight 300 is in g 300.1 This weight 300.1 is in g 300.12 This weight 300.12 is in g 300.123 This weight 300.123 is in g 300.1234 This weight 300.1234 is in g Using: ="This weight "&INT(A1)&IF(ISERROR(FIND(".",A1)),"","."&MID(A1,F IND(".",A1)+1,LEN(A1)-FIN* *D(".",A1)))&" is in "&A2 But, I have 2 areas I would like to refine it. 1 If I use a negative number in A1, I get 1 added to the number like this: -0.5 becomes -1.5 and I dont want that 2 I need this to work when using zeroes as trailing numbers, not always fractional decimal places: 300.00 instead of 300.12 currently it sees 300.00 as just 300. Can anyone help with this? Thanks in advance, Aaron. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula text and decimal place
Hi Bob,
Your suggestion does not cater for preservation of the Decimal Placings of the original number in the final statement. Can you elaborate any more please? Cheers, Aaron. Bob Phillips wrote: I am not clear as to why Trevor's formula is so complex, as this seems to do it ="This weight "&A1&" is in "&A2 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Aaron" wrote in message ups.com... Hi, Trevor Shuttleworth kindly wrote this formula for me: A1=300 A2=g 300 This weight 300 is in g 300.1 This weight 300.1 is in g 300.12 This weight 300.12 is in g 300.123 This weight 300.123 is in g 300.1234 This weight 300.1234 is in g Using: ="This weight "&INT(A1)&IF(ISERROR(FIND(".",A1)),"","."&MID(A1,F IND(".",A1)+1,LEN(A1)-FIN* *D(".",A1)))&" is in "&A2 But, I have 2 areas I would like to refine it. 1 If I use a negative number in A1, I get 1 added to the number like this: -0.5 becomes -1.5 and I dont want that 2 I need this to work when using zeroes as trailing numbers, not always fractional decimal places: 300.00 instead of 300.12 currently it sees 300.00 as just 300. Can anyone help with this? Thanks in advance, Aaron. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula text and decimal place
it preserves it for me.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Aaron" wrote in message oups.com... Hi Bob, Your suggestion does not cater for preservation of the Decimal Placings of the original number in the final statement. Can you elaborate any more please? Cheers, Aaron. Bob Phillips wrote: I am not clear as to why Trevor's formula is so complex, as this seems to do it ="This weight "&A1&" is in "&A2 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Aaron" wrote in message ups.com... Hi, Trevor Shuttleworth kindly wrote this formula for me: A1=300 A2=g 300 This weight 300 is in g 300.1 This weight 300.1 is in g 300.12 This weight 300.12 is in g 300.123 This weight 300.123 is in g 300.1234 This weight 300.1234 is in g Using: ="This weight "&INT(A1)&IF(ISERROR(FIND(".",A1)),"","."&MID(A1,F IND(".",A1)+1,LEN(A1)-FIN* *D(".",A1)))&" is in "&A2 But, I have 2 areas I would like to refine it. 1 If I use a negative number in A1, I get 1 added to the number like this: -0.5 becomes -1.5 and I dont want that 2 I need this to work when using zeroes as trailing numbers, not always fractional decimal places: 300.00 instead of 300.12 currently it sees 300.00 as just 300. Can anyone help with this? Thanks in advance, Aaron. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula text and decimal place
Hi Bob,
Well it doesent for me. What should I check to get the same results you do? I dont understand how a simple tying of cells together with &A1& etc can preserve the decimal places in A1. This is why Trevor wrote the formula he did. Cheers, Aaron. Bob Phillips wrote: it preserves it for me. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Aaron" wrote in message oups.com... Hi Bob, Your suggestion does not cater for preservation of the Decimal Placings of the original number in the final statement. Can you elaborate any more please? Cheers, Aaron. Bob Phillips wrote: I am not clear as to why Trevor's formula is so complex, as this seems to do it ="This weight "&A1&" is in "&A2 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Aaron" wrote in message ups.com... Hi, Trevor Shuttleworth kindly wrote this formula for me: A1=300 A2=g 300 This weight 300 is in g 300.1 This weight 300.1 is in g 300.12 This weight 300.12 is in g 300.123 This weight 300.123 is in g 300.1234 This weight 300.1234 is in g Using: ="This weight "&INT(A1)&IF(ISERROR(FIND(".",A1)),"","."&MID(A1,F IND(".",A1)+1,LEN(A1)-FIN* *D(".",A1)))&" is in "&A2 But, I have 2 areas I would like to refine it. 1 If I use a negative number in A1, I get 1 added to the number like this: -0.5 becomes -1.5 and I dont want that 2 I need this to work when using zeroes as trailing numbers, not always fractional decimal places: 300.00 instead of 300.12 currently it sees 300.00 as just 300. Can anyone help with this? Thanks in advance, Aaron. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to place a decimal after 3rd digit | Excel Discussion (Misc queries) | |||
| Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
| Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
| changing to two place decimal | Excel Worksheet Functions | |||
| Fixed decimal place | Setting up and Configuration of Excel | |||
Linear Mode
