Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
I have a column of cells with numbers like 5.10, 4.10, and whole integers
like 60,65,70,etc. the whole numbers are in inches and the fractions are in feet and inches like 5feet and ten inches. If I want all the numbers to appear in inches format in the next column ,what formula do I apply.That is the values inches should remain as they are and the values in feet should be converted into inches and displayed. The feet measurements are below 9 feet in value. any help in this regard will be appreciated very much. thank you guys and have a nice day steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
Wouldn't just =INT(A1)*12+MOD(A1,1)*100
I don't think so. According the the post, values without decimal places are already stated in inches: the whole numbers are in inches and the fractions are in feet and inches like 5feet and ten inches<< So you wouldn't want 10 converted to 12. But you would want 10.4 converted to 124. *********** Regards, Ron XL2002, WinXP "Sandy Mann" wrote: "Ron Coderre" wrote in message ... B1: =IF(INT(A1)=A1,A1,INT(A1)*12+MOD(A1,1)*100) Wouldn't just =INT(A1)*12+MOD(A1,1)*100 work just as well? -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Ron Coderre" wrote in message ... Try something like this: For a value in A1 B1: =IF(INT(A1)=A1,A1,INT(A1)*12+MOD(A1,1)*100) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "steve" wrote: I have a column of cells with numbers like 5.10, 4.10, and whole integers like 60,65,70,etc. the whole numbers are in inches and the fractions are in feet and inches like 5feet and ten inches. If I want all the numbers to appear in inches format in the next column ,what formula do I apply.That is the values inches should remain as they are and the values in feet should be converted into inches and displayed. The feet measurements are below 9 feet in value. any help in this regard will be appreciated very much. thank you guys and have a nice day steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
So you wouldn't want 10 converted to 12.
But you would want 10.4 converted to 124. ....That should have been So you wouldn't want 10 converted to 120. But you would want 10.4 converted to 124. (is it Friday, yet?) *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Wouldn't just =INT(A1)*12+MOD(A1,1)*100 I don't think so. According the the post, values without decimal places are already stated in inches: the whole numbers are in inches and the fractions are in feet and inches like 5feet and ten inches<< So you wouldn't want 10 converted to 12. But you would want 10.4 converted to 124. *********** Regards, Ron XL2002, WinXP "Sandy Mann" wrote: "Ron Coderre" wrote in message ... B1: =IF(INT(A1)=A1,A1,INT(A1)*12+MOD(A1,1)*100) Wouldn't just =INT(A1)*12+MOD(A1,1)*100 work just as well? -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Ron Coderre" wrote in message ... Try something like this: For a value in A1 B1: =IF(INT(A1)=A1,A1,INT(A1)*12+MOD(A1,1)*100) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "steve" wrote: I have a column of cells with numbers like 5.10, 4.10, and whole integers like 60,65,70,etc. the whole numbers are in inches and the fractions are in feet and inches like 5feet and ten inches. If I want all the numbers to appear in inches format in the next column ,what formula do I apply.That is the values inches should remain as they are and the values in feet should be converted into inches and displayed. The feet measurements are below 9 feet in value. any help in this regard will be appreciated very much. thank you guys and have a nice day steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
Think this works =IF(INT(A1)=A1,A1,INT(A1)*12+MOD(A1,1)*12) VBA noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569139 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
Yes you're quite right - I would be better at this game if I could read. <g
Just for fun an alternative could be: =INT(A1)*12^(MOD(A1,1)0)+MOD(A1,1)*100 -- Regards, Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Ron Coderre" wrote in message ... So you wouldn't want 10 converted to 12. But you would want 10.4 converted to 124. ...That should have been So you wouldn't want 10 converted to 120. But you would want 10.4 converted to 124. (is it Friday, yet?) *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Wouldn't just =INT(A1)*12+MOD(A1,1)*100 I don't think so. According the the post, values without decimal places are already stated in inches: the whole numbers are in inches and the fractions are in feet and inches like 5feet and ten inches<< So you wouldn't want 10 converted to 12. But you would want 10.4 converted to 124. *********** Regards, Ron XL2002, WinXP "Sandy Mann" wrote: "Ron Coderre" wrote in message ... B1: =IF(INT(A1)=A1,A1,INT(A1)*12+MOD(A1,1)*100) Wouldn't just =INT(A1)*12+MOD(A1,1)*100 work just as well? -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Ron Coderre" wrote in message ... Try something like this: For a value in A1 B1: =IF(INT(A1)=A1,A1,INT(A1)*12+MOD(A1,1)*100) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "steve" wrote: I have a column of cells with numbers like 5.10, 4.10, and whole integers like 60,65,70,etc. the whole numbers are in inches and the fractions are in feet and inches like 5feet and ten inches. If I want all the numbers to appear in inches format in the next column ,what formula do I apply.That is the values inches should remain as they are and the values in feet should be converted into inches and displayed. The feet measurements are below 9 feet in value. any help in this regard will be appreciated very much. thank you guys and have a nice day steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
Ron,
Your system clock seems to be off by a day. Please fix it. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
On 7 Aug 2006 20:26:36 -0700, "Harlan Grove" wrote:
Ron, Your system clock seems to be off by a day. Please fix it. Yes, someone pointed that out to me yesterday. I don't know how it got changed but it seems to be OK now (since I corrected it last night). I'll have to keep a closer eye on it. --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
On Mon, 7 Aug 2006 12:02:53 -0700, steve
wrote: I have a column of cells with numbers like 5.10, 4.10, and whole integers like 60,65,70,etc. the whole numbers are in inches and the fractions are in feet and inches like 5feet and ten inches. If I want all the numbers to appear in inches format in the next column ,what formula do I apply.That is the values inches should remain as they are and the values in feet should be converted into inches and displayed. The feet measurements are below 9 feet in value. any help in this regard will be appreciated very much. thank you guys and have a nice day steve If you happen to have the Analysis ToolPak installed, you could use: =IF(A1=INT(A1),A1,DOLLARDE(A1,12)*12) otherwise =IF(A1=INT(A1),A1,INT(A1)*12+MOD(A1,1)*100) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |