If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to make Excel run limited number of formulas on a given worksh | John | General Discussion | 0 | January 12th, 2005 04:29 PM |
importing data and formulas into Excel | Eric | General Discussion | 2 | August 25th, 2004 12:12 AM |
Trouble with protected sheets & formulas | Peter Bernadyne | General Discussion | 6 | August 11th, 2004 02:54 PM |
Formulas | Tonya Morris | Worksheet Functions | 3 | January 12th, 2004 09:41 PM |
Copying formulas with ranges | Carla S | Worksheet Functions | 2 | December 18th, 2003 07:06 PM |