Page 1 of 2 12 LastLast
Results 1 to 10 of 12
  1. #1
    6146-B Billy T's Avatar
    Join Date
    Dec 2004
    Location
    Middle Earth
    Posts
    6,887

    Default Excel column division question

    Hi Team

    I have to convert several columns of figures by a constant, and the Excel help files can't understand my questions. There doesn't seem to be anything on this topic in either of my "How to" manuals either.

    I have three columns containing about 300 values each that I want to divide by 5000 to effect a unit conversion, and another two similarly sized columns that I need to divide by 1000 to convert to another unit.

    Can anybody give me simple instructions on how to do this please? (and I mean simple, my ability with any kind of formulas is negligible).

    Cheers

    Billy 8-{)
    Some days it's not even worth chewing through my restraints!

  2. #2

    Default Re: Excel column division question

    I think there are heaps of ways of doing this, but this is the simplist I can think of.

    Column to be changed, starts in A1.
    The number (5000) in B1.

    The answer in C1
    Pull down the C column to match A column.

    C1 formula is =A1/$B$1

    $ locks the cell reference so that when you drag down it always stays the same.

    Not elegant but simple
    Whats new on Stuffucanuse.com, and the messageboard?

  3. #3
    6146-B Billy T's Avatar
    Join Date
    Dec 2004
    Location
    Middle Earth
    Posts
    6,887

    Default Re: Excel column division question

    Simple it may be, but that might as well be chinese I'm afraid, I have absolutely no idea what you mean. I have zero experience in manipulating data in Excel and I had hoped I could just highlight a column and perform magic.

    Thanks for trying, but it looks like I might be fighting a lost cause here.

    Cheers

    Billy <8-{(
    Some days it's not even worth chewing through my restraints!

  4. #4
    Mostly harmless member kingdragonfly's Avatar
    Join Date
    Dec 2005
    Location
    Wellington NZ
    Posts
    1,066

    Default Re: Excel column division question

    As netchicken mentions, you'll need some type of formula. It's a little bit daunting if you haven't seen it in use before, but there's heaps of tutorials on the web.

    This part of Excel hasn't changed much in 10 years, whether it's an Excel 97 tutorial or Office 2003.

    Might want to search Google for "Excel tutorial formulas"

  5. #5
    ~ >*()))><( andrew93's Avatar
    Join Date
    Dec 2004
    Posts
    1,556

    Default Re: Excel column division question


  6. #6
    Beam me up Scotty Capt Jimbo's Avatar
    Join Date
    Dec 2004
    Posts
    20

    Default Re: Excel column division question

    I'll try and list the steps as simply as I can
    In an empty column preferably in the same row as the 1st the top of your existing comlumn.
    Type the formula =A1/5000
    Change A1 as appropriate to correspond with your own spreadsheet.
    Use the same formula for all conversions except change the 5000 to 1000 for the other two columns.
    Once you have a single cell showing correctly it is a relatively easy matter to "Fill Down"
    Excel help should assist but if you get stuck come back for more advice.
    PM me if you want and I'll talk you through it.

    Capt Jimbo

  7. #7
    Pedant and proud of it
    Join Date
    Dec 2004
    Location
    Christchurch
    Posts
    6,047

    Default Re: Excel column division question

    Try Omnitab (which dates from the 1970s and is still available free from NIST ... which used to be the NBS). The code would go something like

    DIVIDE columns 1,2,3 by 5000.0 and put into cols 1,2, and 3
    DIVIDE cols 4 5 by 1000.0 and put into cols 4 and 5

    It's like COBOL for scientists ... with absolutely reliable statistical routines.

  8. #8
    Network Engineer SolMiester's Avatar
    Join Date
    Feb 2005
    Location
    Napier
    Posts
    8,184

    Default Re: Excel column division question

    Haha Graham, showing your age and even more, your geekness there, me ol china!
    HOME-LianLi PC-9F,ASRock P67Pro3, i5 2500k @4Ghz, 8Gb HyperX, ASUS GTX660 OC, Corsair Force 120 SSD, HP zIPS22", HOME SERVER HP ML110G6 HOST-Plex\Ubuntu\8

  9. #9
    Pedant and proud of it
    Join Date
    Dec 2004
    Location
    Christchurch
    Posts
    6,047

    Default Re: Excel column division question

    Check it out. The PDF manual's a bit big (41 MB), but the executable is small. It's actually easy to use and, best of all, Omnitab 80 is free.

  10. #10
    Senior Member
    Join Date
    Dec 2004
    Location
    Wellington, NZ
    Posts
    353

    Default Re: Excel column division question

    Hi Billy, post again if your having trouble with the formulas. If your intention is to replace the existing constant values with new constant values then you can use Excel's paste special operation to do this.

    Try on some example cells in a new sheet until your comfortable.

    1. Type in the amount you want multiple, divide, subtract or add by (eg 5000)
    2. Select this cell and click the copy button on the toolbar or Edit|Copy from the menu
    3. Select your taget cells with your mouse. Note do not select a whole column just the data or you'll end up with lots of cells with 0 in them.
    4. Select Edit|Paste Special from the menu.
    5. Leave the paste operation as All and change the Operation from None to Divide (or Add, Multiply or Subtract as needed) and click OK

    Note what I described above replaces your old values so if you never need to use these again then thats fine. If however you might need the "raw" data then use formulas in another column.

    regards,
    Graham

Similar Threads

  1. transforming excel row to column
    By bpt2 in forum PressF1
    Replies: 8
    Last Post: 25-10-2006, 02:59 PM
  2. excel column puzzle
    By santae in forum PressF1
    Replies: 21
    Last Post: 20-11-2002, 03:49 PM
  3. Replies: 1
    Last Post: 18-11-2002, 09:23 AM
  4. Printing Column Headings in Excel
    By in forum PressF1
    Replies: 0
    Last Post: 15-06-2000, 11:19 AM
  5. Replies: 0
    Last Post: 02-09-1999, 01:21 AM

Posting Permissions

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