FORMULA HELP

[steve]

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

[Sandy Mann]

"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...

> 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

[email protected]
[email protected] with @tiscali.co.uk


"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> 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

[Ron Coderre]

> 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" <[email protected]> wrote in message
> news:[email protected]...
>
> > 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
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Ron Coderre" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>
>

[Ron Coderre]

> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >
> > > 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
> >
> > [email protected]
> > [email protected] with @tiscali.co.uk
> >
> >
> > "Ron Coderre" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >
> >

[VBA Noob]

Think this works

=IF(INT(A1)=A1,A1,INT(A1)*12+MOD(A1,1)*12)

VBA noob

[Sandy Mann]

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

[email protected]
[email protected] with @tiscali.co.uk


"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
>> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >
>> > > 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
>> >
>> > [email protected]
>> > [email protected] with @tiscali.co.uk
>> >
>> >
>> > "Ron Coderre" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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
>> >
>> >
>> >
>> >

[Ron Rosenfeld]

On Mon, 7 Aug 2006 12:02:53 -0700, steve <[email protected]>
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

[Harlan Grove]

Ron,

Your system clock seems to be off by a day. Please fix it.

[Ron Rosenfeld]

On 7 Aug 2006 20:26:36 -0700, "Harlan Grove" <[email protected]> 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

[Ron Rosenfeld]

On 7 Aug 2006 20:26:36 -0700, "Harlan Grove" <[email protected]> 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