Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Int Iserror Len Mid Find formula
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.worksheet.functions
|
|||
|
|||
Int Iserror Len Mid Find formula
Maybe this:
="This weight "&TEXT(A1,"0.00##")&" is in "&A2 Biff "Aaron" wrote in message oups.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 | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
copy and pasting a find all list into another column | Excel Discussion (Misc queries) | |||
FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES | Excel Worksheet Functions |