#1   Report Post  
Lewis Shanks
 
Posts: n/a
Default Formulas

Is there a way to set up a formula to convert metric dimensions (in this
case for carpets) to feet. For example:
the current dimensions in cells are in meters as follows:
2.50 ? 3.50


The actual conversion would be 8.20 x 11.48 (this is what I would like to
be able to convert the above cell to) using the conversion factor or 3.281
ft/meter.

Thanks
Lewis Shanks


  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

Do you have both values in one cell?

If you do, I'd put each value into a separate column.

Then you could use a formula like:

=A1*3.281

or even:

=CONVERT(A1,"m","ft")

You can see all the units that you can use in excel's help for =convert()

And you'll have to make sure that the analysis toolpak is loaded
Tools|addins
else you'll get #name? errors back.

Then you could use:

Lewis Shanks wrote:

Is there a way to set up a formula to convert metric dimensions (in this
case for carpets) to feet. For example:
the current dimensions in cells are in meters as follows:
2.50 ? 3.50

The actual conversion would be 8.20 x 11.48 (this is what I would like to
be able to convert the above cell to) using the conversion factor or 3.281
ft/meter.

Thanks
Lewis Shanks


--

Dave Peterson
  #3   Report Post  
Lewis Shanks
 
Posts: n/a
Default

Sorry, mine was not clear earlier:

the metric column is currently headed as: Standard Sizes (meters), and the
typical format for each cell is:
2.50 x 3.50. In other words the length is 2.50m and the width is 3.50m.

I want to change it to have the same format but calculated in feet per
dimension, so that the new column might be headed Standard Sizes (feet), and
a typical cell would then read 8.20 x 11.48, i.e. the length and width in
feet.

I am trying to avoid having to retype or copy and past each dimension as
you suggested in two separate columns.

Hope that this clarifies my situation.

Thanks
Lewis

"Dave Peterson" wrote in message
...
Do you have both values in one cell?

If you do, I'd put each value into a separate column.

Then you could use a formula like:

=A1*3.281

or even:

=CONVERT(A1,"m","ft")

You can see all the units that you can use in excel's help for =convert()

And you'll have to make sure that the analysis toolpak is loaded
Tools|addins
else you'll get #name? errors back.

Then you could use:

Lewis Shanks wrote:

Is there a way to set up a formula to convert metric dimensions (in this
case for carpets) to feet. For example:
the current dimensions in cells are in meters as follows:
2.50 ? 3.50

The actual conversion would be 8.20 x 11.48 (this is what I would like
to
be able to convert the above cell to) using the conversion factor or
3.281
ft/meter.

Thanks
Lewis Shanks


--

Dave Peterson



  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

Your life will be lots easier if you separate the values into different cells.

You could use Data|Text to columns
using delimited (by X and space)
then drop it into 2 separate columns
then use a formula for each conversion
and maybe combine them later with a formula like:
=C1&" x "&D1

or even
=text(c1,"0.00")&" X "&text(d1,"0.00")

But this formula worked ok for me with
2.50 X 3.50
in A1

=TEXT(CONVERT(--LEFT(A1,SEARCH("x",A1)-1),"m","ft"),"0.00")&" X "
&TEXT(CONVERT(--MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="x"),
ROW(INDIRECT("1:"&LEN(A1))))+1,255),"m","ft"),"0.0 0")


All one cell.

(I'd use the extra helper cells/columns and hide them if I didn't want to see
them.)








Lewis Shanks wrote:

Sorry, mine was not clear earlier:

the metric column is currently headed as: Standard Sizes (meters), and the
typical format for each cell is:
2.50 x 3.50. In other words the length is 2.50m and the width is 3.50m.

I want to change it to have the same format but calculated in feet per
dimension, so that the new column might be headed Standard Sizes (feet), and
a typical cell would then read 8.20 x 11.48, i.e. the length and width in
feet.

I am trying to avoid having to retype or copy and past each dimension as
you suggested in two separate columns.

Hope that this clarifies my situation.

Thanks
Lewis

"Dave Peterson" wrote in message
...
Do you have both values in one cell?

If you do, I'd put each value into a separate column.

Then you could use a formula like:

=A1*3.281

or even:

=CONVERT(A1,"m","ft")

You can see all the units that you can use in excel's help for =convert()

And you'll have to make sure that the analysis toolpak is loaded
Tools|addins
else you'll get #name? errors back.

Then you could use:

Lewis Shanks wrote:

Is there a way to set up a formula to convert metric dimensions (in this
case for carpets) to feet. For example:
the current dimensions in cells are in meters as follows:
2.50 ? 3.50

The actual conversion would be 8.20 x 11.48 (this is what I would like
to
be able to convert the above cell to) using the conversion factor or
3.281
ft/meter.

Thanks
Lewis Shanks


--

Dave Peterson


--

Dave Peterson
  #5   Report Post  
Lewis Shanks
 
Posts: n/a
Default

Dave:
Dave:

Wow!

All of that is way beyond me, but I do appreciate it. I think that I will do
as suggested and take the time to more each dimension to a respective column
and then convert with a simple formula.

Thanks very much

Lewis

"Dave Peterson" wrote in message
...
Your life will be lots easier if you separate the values into different
cells.

You could use Data|Text to columns
using delimited (by X and space)
then drop it into 2 separate columns
then use a formula for each conversion
and maybe combine them later with a formula like:
=C1&" x "&D1

or even
=text(c1,"0.00")&" X "&text(d1,"0.00")

But this formula worked ok for me with
2.50 X 3.50
in A1

=TEXT(CONVERT(--LEFT(A1,SEARCH("x",A1)-1),"m","ft"),"0.00")&" X "
&TEXT(CONVERT(--MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="x"),
ROW(INDIRECT("1:"&LEN(A1))))+1,255),"m","ft"),"0.0 0")


All one cell.

(I'd use the extra helper cells/columns and hide them if I didn't want to
see
them.)








Lewis Shanks wrote:

Sorry, mine was not clear earlier:

the metric column is currently headed as: Standard Sizes (meters), and
the
typical format for each cell is:
2.50 x 3.50. In other words the length is 2.50m and the width is 3.50m.

I want to change it to have the same format but calculated in feet per
dimension, so that the new column might be headed Standard Sizes (feet),
and
a typical cell would then read 8.20 x 11.48, i.e. the length and width in
feet.

I am trying to avoid having to retype or copy and past each dimension as
you suggested in two separate columns.

Hope that this clarifies my situation.

Thanks
Lewis

"Dave Peterson" wrote in message
...
Do you have both values in one cell?

If you do, I'd put each value into a separate column.

Then you could use a formula like:

=A1*3.281

or even:

=CONVERT(A1,"m","ft")

You can see all the units that you can use in excel's help for
=convert()

And you'll have to make sure that the analysis toolpak is loaded
Tools|addins
else you'll get #name? errors back.

Then you could use:

Lewis Shanks wrote:

Is there a way to set up a formula to convert metric dimensions (in
this
case for carpets) to feet. For example:
the current dimensions in cells are in meters as follows:
2.50 ? 3.50

The actual conversion would be 8.20 x 11.48 (this is what I would
like
to
be able to convert the above cell to) using the conversion factor or
3.281
ft/meter.

Thanks
Lewis Shanks

--

Dave Peterson


--

Dave Peterson





  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default

I would!

It really makes life easier if you separate each field into its own column.

Good idea for names (first, last, MI, salutation,... all in separate cells).
(Just in case you ever have to set up a mini-database.)

Lewis Shanks wrote:

Dave:
Dave:

Wow!

All of that is way beyond me, but I do appreciate it. I think that I will do
as suggested and take the time to more each dimension to a respective column
and then convert with a simple formula.

Thanks very much

Lewis

"Dave Peterson" wrote in message
...
Your life will be lots easier if you separate the values into different
cells.

You could use Data|Text to columns
using delimited (by X and space)
then drop it into 2 separate columns
then use a formula for each conversion
and maybe combine them later with a formula like:
=C1&" x "&D1

or even
=text(c1,"0.00")&" X "&text(d1,"0.00")

But this formula worked ok for me with
2.50 X 3.50
in A1

=TEXT(CONVERT(--LEFT(A1,SEARCH("x",A1)-1),"m","ft"),"0.00")&" X "
&TEXT(CONVERT(--MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="x"),
ROW(INDIRECT("1:"&LEN(A1))))+1,255),"m","ft"),"0.0 0")


All one cell.

(I'd use the extra helper cells/columns and hide them if I didn't want to
see
them.)








Lewis Shanks wrote:

Sorry, mine was not clear earlier:

the metric column is currently headed as: Standard Sizes (meters), and
the
typical format for each cell is:
2.50 x 3.50. In other words the length is 2.50m and the width is 3.50m.

I want to change it to have the same format but calculated in feet per
dimension, so that the new column might be headed Standard Sizes (feet),
and
a typical cell would then read 8.20 x 11.48, i.e. the length and width in
feet.

I am trying to avoid having to retype or copy and past each dimension as
you suggested in two separate columns.

Hope that this clarifies my situation.

Thanks
Lewis

"Dave Peterson" wrote in message
...
Do you have both values in one cell?

If you do, I'd put each value into a separate column.

Then you could use a formula like:

=A1*3.281

or even:

=CONVERT(A1,"m","ft")

You can see all the units that you can use in excel's help for
=convert()

And you'll have to make sure that the analysis toolpak is loaded
Tools|addins
else you'll get #name? errors back.

Then you could use:

Lewis Shanks wrote:

Is there a way to set up a formula to convert metric dimensions (in
this
case for carpets) to feet. For example:
the current dimensions in cells are in meters as follows:
2.50 ? 3.50

The actual conversion would be 8.20 x 11.48 (this is what I would
like
to
be able to convert the above cell to) using the conversion factor or
3.281
ft/meter.

Thanks
Lewis Shanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #7   Report Post  
Lewis Shanks
 
Posts: n/a
Default

Dave:
Thanks
I fully agree, but this was the way I originally received it from my Moscow
office, so I will have to do the conversion per column myself. It's not a
big deal, but they are not even familiar with the most basic formulas.
Thanks
Lewis
"Dave Peterson" wrote in message
...
I would!

It really makes life easier if you separate each field into its own
column.

Good idea for names (first, last, MI, salutation,... all in separate
cells).
(Just in case you ever have to set up a mini-database.)

Lewis Shanks wrote:

Dave:
Dave:

Wow!

All of that is way beyond me, but I do appreciate it. I think that I will
do
as suggested and take the time to more each dimension to a respective
column
and then convert with a simple formula.

Thanks very much

Lewis

"Dave Peterson" wrote in message
...
Your life will be lots easier if you separate the values into different
cells.

You could use Data|Text to columns
using delimited (by X and space)
then drop it into 2 separate columns
then use a formula for each conversion
and maybe combine them later with a formula like:
=C1&" x "&D1

or even
=text(c1,"0.00")&" X "&text(d1,"0.00")

But this formula worked ok for me with
2.50 X 3.50
in A1

=TEXT(CONVERT(--LEFT(A1,SEARCH("x",A1)-1),"m","ft"),"0.00")&" X "
&TEXT(CONVERT(--MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="x"),
ROW(INDIRECT("1:"&LEN(A1))))+1,255),"m","ft"),"0.0 0")


All one cell.

(I'd use the extra helper cells/columns and hide them if I didn't want
to
see
them.)








Lewis Shanks wrote:

Sorry, mine was not clear earlier:

the metric column is currently headed as: Standard Sizes (meters), and
the
typical format for each cell is:
2.50 x 3.50. In other words the length is 2.50m and the width is
3.50m.

I want to change it to have the same format but calculated in feet per
dimension, so that the new column might be headed Standard Sizes
(feet),
and
a typical cell would then read 8.20 x 11.48, i.e. the length and width
in
feet.

I am trying to avoid having to retype or copy and past each dimension
as
you suggested in two separate columns.

Hope that this clarifies my situation.

Thanks
Lewis

"Dave Peterson" wrote in message
...
Do you have both values in one cell?

If you do, I'd put each value into a separate column.

Then you could use a formula like:

=A1*3.281

or even:

=CONVERT(A1,"m","ft")

You can see all the units that you can use in excel's help for
=convert()

And you'll have to make sure that the analysis toolpak is loaded
Tools|addins
else you'll get #name? errors back.

Then you could use:

Lewis Shanks wrote:

Is there a way to set up a formula to convert metric dimensions (in
this
case for carpets) to feet. For example:
the current dimensions in cells are in meters as follows:
2.50 ? 3.50

The actual conversion would be 8.20 x 11.48 (this is what I would
like
to
be able to convert the above cell to) using the conversion factor
or
3.281
ft/meter.

Thanks
Lewis Shanks

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formulas not working John Lovin Excel Discussion (Misc queries) 3 January 18th 05 11:50 PM
How to make Excel run limited number of formulas on a given worksh John Excel Discussion (Misc queries) 0 January 12th 05 05:29 PM
Way to make Excel only run certain formulas on a worksheet? jrusso Excel Discussion (Misc queries) 0 January 12th 05 05:23 PM
Why won't formulas obey vertical alignment commands? imoux1 Excel Discussion (Misc queries) 2 December 28th 04 05:45 PM
delete values in several cells without deleting the formulas dranreb Excel Discussion (Misc queries) 4 December 9th 04 02:15 AM


All times are GMT +1. The time now is 02:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"