+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 28

Converting spreadsheets from inches to centimeters

  1. #1
    Registered User
    Join Date
    03-05-2018
    Location
    Toledo, OH
    MS-Off Ver
    Office 2007
    Posts
    21

    Converting spreadsheets from inches to centimeters

    I use Excel 2007 on Windows 7 Pro (64 bit), SP1. I have a spreadsheet with about 100 rows and 30 columns; i.e., 3000 data cells. Data is manually entered into the data cells, using 'inches' as the measurement system. There are also about 10 columns of summation results. This spreadsheet uses the Table format. There are several pages of linked Charts that are created based on this data.

    I have a friend who wants this information in centimeters. All the methods I knew of (using Convert, etc.) required having additional cells, which is a major task.

    JE McGimpsey suggested one way in a 2005 thread, "Convert my entire spreadsheet from inches to centimeters". But that thread appears to be closed.

    He said: "Enter 2.54 in an empty cell. Copy the cell. Select your values to
    convert. Choose Edit/Paste Special, selecting the Values and Multiply
    radio buttons."

    So I cloned my original spreadsheet onto a new tab, and used his procedure to quickly populate the new spreadsheet with centimeter dimensions. That worked great.

    But there was a problem. I have many empty cells. Sometimes no data was taken on a day, or data was missed at a specific time. My original spreadsheet, and the cloned spreadsheet display those cells as being empty. But in the cloned spreadsheet, his method actually has a 'hidden' 0(zero) in each of the supposedly empty cells. You can only see that 0 if you click on the specific cell.

    For some tasks that might not be a problem. But I have multiple charts that use the data on the cloned spreadsheet. So initially those charts displayed all supposedly empty cells as a 0, rather than ignoring them. To fix the problem, I had to click on every cell which appeared to be empty, and if it contained a 0, delete it.

    Does anyone know why his method creates this 0, and if there is some way I can modify his procedure to make sure that cells which display as empty are really empty?


    Harry

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    22,142

    Re: Converting spreadsheets from inches to centimeters

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016/Work 2013 Pro Plus
    Posts
    1,631

    Re: Converting spreadsheets from inches to centimeters

    Quote Originally Posted by bex1210 View Post
    All the methods I knew of (using Convert, etc.) required having additional cells, which is a major task.
    Why is having additional cells a major task?

    Attach a sample of your data showing what you want to achieve mocked-up.

    Go Advanced -> Manage Attachments -> Upload

  4. #4
    Registered User
    Join Date
    03-05-2018
    Location
    Toledo, OH
    MS-Off Ver
    Office 2007
    Posts
    21

    Re: Converting spreadsheets from inches to centimeters

    I already have 3000 data cells. Using 'Convert' or simple multiplication requires an empty cell for EACH of the cells I want to convert, or 3000 more cells. The procedure McGimpsey suggested only requires one extra cell.

    The moderator, AliGW, said I need to attach a complete, non proprietary spreadsheet. That will take me a day or so to create.

    Harry

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    22,142

    Re: Converting spreadsheets from inches to centimeters

    Providing a workbook is optional, however it will make helping you a lot easier. It might be the difference between getting help and not getting help. Up to you!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,946

    Re: Converting spreadsheets from inches to centimeters

    We only need a SMALL sample of what you are working with, maybe 10-20 rows. Just enough to show what you are working with.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,940

    Re: Converting spreadsheets from inches to centimeters

    Quote Originally Posted by bex1210 View Post
    Data is manually entered into the data cells, using 'inches' as the measurement system. There are also about 10 columns of summation results.
    I assume from this that it's only the manually entered data which needs to be converted to centimetres - the rest will automatically calculate.

    Quote Originally Posted by bex1210 View Post
    "Enter 2.54 in an empty cell. Copy the cell. Select your values to convert. Choose Edit/Paste Special, selecting the Values and Multiply radio buttons."
    It's this 'select your values' part which you want to work on. Based on the assumption above, all the manually entered data is constants. Assuming that that's correct and that there aren't any other numerical constants mixed in with that data, you just need to select only constants in your range. So try this:

    Enter 2.54 in a spare cell and copy it.
    Select the range containing this manually entered data.
    Press F5 to open the Go To dialogue box then click Special.
    Select Constants and click OK. Now only the manually entered data should be selected.
    Now use Paste Special and Multiply.

    Make sure you try this first on a copy of the file/sheet, just in case the assumption above turns out not to be true!
    Regards,
    Aardigspook

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  8. #8
    Registered User
    Join Date
    03-05-2018
    Location
    Toledo, OH
    MS-Off Ver
    Office 2007
    Posts
    21

    Re: Converting spreadsheets from inches to centimeters

    AliGW:

    Thanks for the detailed help about how to attach files. I hope I did it correctly.

    And thanks for suggesting that I just create a simplified spreadsheet. However in this
    case, it takes much more work to create one than to just remove all text that might in
    some way be confidential, so that is what I did.

    I will not repeat my setup and the problem, which are listed in my 1st post.

    Two files are attached. The first contains the 'Paste Special' windows that
    JE McGimpsey suggested. You will see that I checked the two items that he suggested:
    Values and Multiply. There is also a 'Skip Blanks' option. I tried both checking this
    option and leaving it unchecked. That option had no effect on the problem I have.

    The second file is the Excel spreadsheet. It should open to the 1st Tab, 'Measurements'.
    Rows 16 thru 119 are data rows, where data (in inches) is entered manually.
    (This is a Table. I have hidden row 15, which is the Table header row.) For each of the
    rows, data is entered in columns B thru S, and V thru AM. (Columns AP thru AU are
    calculated, based on the manually entered data.) There are several blank rows
    representing missed dates (line 104 represents multiple missed dates), and other rows
    just have a few blank cells that were missed. If you click on any of these blank cells,
    you will see that they are empty.

    The 2nd Tab is 'Charts'. I actually have about 40 linked charts, but I simplified this
    file to only include 2 of them. The top one, 'Left Foot-Front', consists of columns A
    thru D, the bottom one, 'Left Ankle-Below Bone', consists of column A, and columns E
    thru G. Both charts contain values in the 9 to 11 inch range.

    The 3rd tab is 'MeasurementsCm'. I entered 2.54 in Cell N1, and copied it to Rows 16
    thru 119, columns B thru S, and V thru AM. Then I selected the Values and Multiply
    radio buttons in the Paste Special window. Click on any of the blank cells. You will
    see that they are not empty, but contain 0 (zero).

    Now look at the 'ChartsCm' tab. You will see that not only are there values in the 25
    to 30 Cm range, but each entry also has a 0 value! The first row with a 0 value on the
    charts is row 18, 7/24/2018. Click on B18, C18, & D18, and delete the 0 values. Now
    look at the 'Left Foot-Front' chart. 7/24/2018 no longer displays the 0 value!

    So my question is: How do I prevent the 0 value from appearing in the blank cells of the
    'MeasurementsCm' tab? They should be empty, like the blank cells on the 'Measurements'
    tab.


    Harry
    Attached Images Attached Images
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-05-2018
    Location
    Toledo, OH
    MS-Off Ver
    Office 2007
    Posts
    21

    Re: Converting spreadsheets from inches to centimeters

    Quote Originally Posted by Aardigspook View Post
    I assume from this that it's only the manually entered data which needs to be converted to centimetres - the rest will automatically calculate.
    Yes, you are correct. The other columns calculate automatically.

    It's this 'select your values' part which you want to work on. Based on the assumption above, all the manually entered data is constants. Assuming that that's correct and that there aren't any other numerical constants mixed in with that data, you just need to select only constants in your range. So try this:

    Enter 2.54 in a spare cell and copy it.
    Select the range containing this manually entered data.
    Press F5 to open the Go To dialogue box then click Special.
    Select Constants and click OK. Now only the manually entered data should be selected.
    Now use Paste Special and Multiply.

    Make sure you try this first on a copy of the file/sheet, just in case the assumption above turns out not to be true!
    No other constants are mixed in with the data--numerical or otherwise.

    This DOES work. [Don't worry, I tried it on a copy.] But as you noted, the conditions have to be very specific for it to work. What I really don't understand is why this step is necessary: i.e., why the 0 (zero) is created in the 1st place.

    Look at the post I made less than an hour ago addressed to AliGW, where I provide more details and a copy of the spreadsheet.


    Harry

  10. #10
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,940

    Re: Converting spreadsheets from inches to centimeters

    The 0 is created because doing Paste Special → Multiply tells Excel to multiply all selected values by 2.54. Unfortunately, the way the function is programmed into Excel means that it treats blanks as zeroes, so the result becomes 0 (0 x 2.54). The only way I know of, without VBA, to avoid that is to exclude the blanks from the selection before doing the multiplication, by selecting only the other data.

  11. #11
    Registered User
    Join Date
    03-05-2018
    Location
    Toledo, OH
    MS-Off Ver
    Office 2007
    Posts
    21

    Re: Converting spreadsheets from inches to centimeters

    Quote Originally Posted by Aardigspook View Post
    The 0 is created because doing Paste Special → Multiply tells Excel to multiply all selected values by 2.54. Unfortunately, the way the function is programmed into Excel means that it treats blanks as zeroes, so the result becomes 0 (0 x 2.54). The only way I know of, without VBA, to avoid that is to exclude the blanks from the selection before doing the multiplication, by selecting only the other data.
    OK, I can follow what Excel has done. But if that is the way it is programmed, why doesn't checking the 'Skip blanks' option in the Paste Special window solve the problem?


    Harry

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    24,169

    Re: Converting spreadsheets from inches to centimeters

    ...back to the Q. Kersplash asked seemingly ages ago.
    Why are a parallel set of problems out of the question?

    If you don't need them after you've multipled by 2.4 just copy them all, paste as values over the original columns and delete the helper columns.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

    Forum Rules Updated September 2018. Please read them by clicking here.

  13. #13
    Registered User
    Join Date
    03-05-2018
    Location
    Toledo, OH
    MS-Off Ver
    Office 2007
    Posts
    21

    Re: Converting spreadsheets from inches to centimeters

    Quote Originally Posted by Richard Buttrey View Post
    ...back to the Q. Kersplash asked seemingly ages ago.
    Why are a parallel set of problems out of the question?

    If you don't need them after you've multipled by 2.4 just copy them all, paste as values over the original columns and delete the helper columns.
    This was my answer to Kersplash earlier:
    I already have 3000 data cells. Using 'Convert' or simple multiplication requires an empty cell for EACH of the cells I want to convert, or 3000 more cells. The procedure McGimpsey suggested only requires one extra cell.

    In my case it is easier to manually delete the '0' values than to create and remove all the extra data cells.

    There are multiple ways to fix the problem, including what Aardigspook suggested, but each have certain limitations. I am trying to see if there is a way to prevent the problem from occurring at all.


    Harry

  14. #14
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,632

    Re: Converting spreadsheets from inches to centimeters

    Does anyone know why his method creates this 0, and if there is some way I can modify his procedure to make sure that cells which display as empty are really empty?
    With CTR H you can remove all the zero's
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  15. #15
    Registered User
    Join Date
    03-05-2018
    Location
    Toledo, OH
    MS-Off Ver
    Office 2007
    Posts
    21

    Re: Converting spreadsheets from inches to centimeters

    Quote Originally Posted by popipipo View Post
    With CTRL H you can remove all the zero's
    That is quicker and more foolproof than doing it manually, as long as 'Match entire cell contents' is checked.

    I had not thought of that option. Thanks. Or course I would still prefer that it not happen to begin with!


    Harry

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1