| Home |
| Search |
| Today's Posts |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I segment a column of data into size ranges in Excel?
You're welcome!
Biff "Motown Mick" wrote in message ... Biff, That worked fine. I had to do EditPaste Special Values for the denominator as well, because when I did it by just by just copying it as is, the results came out screwed up with an error saying I was dividing by zero. But that worked great, I got it all now, I think. Thanks for all your help. -Mick "Biff" wrote: Ok..... Not sure I understand why you want to do it the way you describe..... What's wrong with: E2 = formula result F2 = =E2/some_number You can paste specialvalues: Select E2 (or the range of formula results) Goto the menu EditCopy Select the destination cell Goto the menu EditPaste specialValuesOK That'll copy/paste the formula results as constants and still leave the original formulas in tact. Biff "Motown Mick" wrote in message ... Biff, Thanks, that worked great. I thought of the "guhzillion" thing in there, I guess that's the simplest and easiest thing to do. Now I am trying to take the results in the E column and divide them by a single constant scalar and display the results in an adjoining column. I know about the "paste special" command. But for some reason, I cannot copy those E column results to another column where I can do that on it, and leave the E results unharmed. Do you know how I can do this? Mick "Biff" wrote: Ok.... 1. I just used a range of A1:A25 for demonstration purposes. Use your actual range whatever it may be, or if you want, use the entire column A:A. 2. You can do a couple of things for this. A. use an upper boundary value that you know you will never exceed. Like 250,000..........10,000,000,000 B. use a separate formula for this last group with just the single boundary: =SUMIF(A:A,"="&C10) Where C10 = 250,000 Biff "Motown Mick" wrote in message ... Biff, That worked fine. A couple of further ques.: 1. I only tried if for a couple of the value ranges that I knew would fall into the area of A1:A25. I'm assuming, the way you set up the syntax, that Excel would disregard entries in A that fell out of that segment. So for instance, for the range 500-749, if all the entries in A that fell into those bounds were in cells of A greater than 25 (say, A26:A99), given the formula you've given me, the sum would be zero. I would like to set it so that it searches the entire column of A for values that fall into the specified ranges. How do I do that? 2. The final value range is open at the top; in other words, I want to sum all values in Col. A that take a value of, say, 250,000 on up to infinity. What would I write in col. D to represent "infinity"? Mick "Biff" wrote: Ok..... Set up some cells to hold your value ranges: ............C...........D.............E..... 1.........0..........249.......formula 2.......250........499.......formula 3.......500........749.......formula Assume the range of numbers is in A1:A25 Enter this formula in E1 and copy down as needed: =SUMIF(A$1:A$25,"="&C1)-SUMIF(A$1:A$25,""&D1) Biff "Motown Mick" wrote in message ... Biff, This is almost like what I want to do except that: 1. Say I'm dealing with Column A. I'm looking for a way to sum by VALUE, not cell designation. So rather than summing A1:A10, A11:A20, I would like to know how to sum all the entries in Column A that actually have an entered data value of, say, 0-249, 250-499, etc. 2. A formula that can be copied and dragged down is not necessary. If I could just get a formula that could be applied to each of those data ranges, that would do the trick for me. Thanks. Motown Mick "Biff" wrote: Hi! Ideally, if there is a formula command that can point to a section of data in a column like this, and perform an operation on it, that I can direct to a particular cell, I would like to know how to do that. There is, but you need to tell us *EXACTLY* what you want to do and tell us *EXACTLY* where the data is. For example, This formula, when copied down, will sum every 10 rows in column A: =SUM(OFFSET(A$1,(ROWS($1:1)-1)*10,,10)) The first cell will sum A1:A10 The next cell will sum A11:A20 The next cell will sum A21:A30 etc etc Biff "Motown Mick" <Motown wrote in message ... I have a column of data listed in ascending order. I would like to pull sections of it out (i.e. 0-10, 11-20, etc.) without having to manually go through the column and highlight that section, copy, paste, etc. Ideally, if there is a formula command that can point to a section of data in a column like this, and perform an operation on it, that I can direct to a particular cell, I would like to know how to do that. I can't figure out what version of Excel I have. It's whatever comes with MS Windows XP. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| transfer data into row cells from column cells in Excel | Excel Discussion (Misc queries) | |||
| Stock data manipulation | Excel Worksheet Functions | |||
| Excel: How to choose data on two separate rows in the same column | Excel Worksheet Functions | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Threaded Mode
