| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 66996 | 2006-03-13 23:00:00 | Excel column division question | Billy T (70) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 437913 | 2006-03-13 23:00:00 | 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-{) |
Billy T (70) | ||
| 437914 | 2006-03-13 23:09:00 | 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 |
netchicken (4843) | ||
| 437915 | 2006-03-13 23:20:00 | 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-{( |
Billy T (70) | ||
| 437916 | 2006-03-14 00:50:00 | 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" |
kingdragonfly (309) | ||
| 437917 | 2006-03-14 00:52:00 | This might help you Billy office.microsoft.com A |
andrew93 (249) | ||
| 437918 | 2006-03-14 00:53:00 | 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 |
Capt Jimbo (17) | ||
| 437919 | 2006-03-14 04:54:00 | 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. :cool: |
Graham L (2) | ||
| 437920 | 2006-03-14 05:04:00 | Haha Graham, showing your age and even more, your geekness there, me ol china! | SolMiester (139) | ||
| 437921 | 2006-03-14 05:32:00 | 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 (www.itl.nist.gov/div898/software/omnitab.html) is free. | Graham L (2) | ||
| 437922 | 2006-03-14 05:36:00 | 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 |
Parry (5696) | ||
| 1 2 | |||||