#1
|
|||
|
|||
hi there,
I am calculating yearly quarter values of from monthly data and I am unable to find the very flexible formula I need. The reason is that I would like my quarter cells to appear next to each other while not disturbing my monthly cells. So I need a formula that looks 3 cells over from the previous one. Example Jan Feb Mar Apr May Jun Jul Aug Sept 46 48 58 26 22 35 58 47 68 SUM SUM SUM Q1 Q2 Q3 152 83 173 Let me know if you need a better explanation. I basically need a formula that skips a known number of cells before recalculating. Example Attached. I would love for the formulas at the bottom (the CDD data) to move down automatically like the ones in the HDD column have (that was manually done) Thanks! |
#2
|
|||
|
|||
Hi there!
I have attached two ways of solving this (in addition one of the solutions is splitted into alternative a and b and c) In "Solution 1" you have to add a column with a formula to the right of your monthly data, see column E (you could of course also place it to the left of the data). Perhaps you don't like that solution... In "Solution 2a" you have to add two columns (with formulas) to the right of your quarterly data, see column F and G. and perhaps you don't like that solution... "Solution 2b" is actually doing the same as "Solution 2a". I just made everything more compact (but more difficult to "read") by integrating everything into one formula. In all solutions, the formula in cell B37 is written in a general form, meaning it can be copied down and also can be copied to the right (to column D for example, as I did in the file). I guess you are familiar with the technique referencing to an area like "$A$8:$A$34". There are several techniques how to avoid changing the "$34" to "$35" when adding one month. One simple technique is to have one blank month at the end, and always insert new month above this line (see "Solution 2c"). I hope this solves the issue as you wanted! Kind regards Bjorn |
#3
|
|||
|
|||
Thanks for the hard work Bjorn, these certainly will work for the data as is. However, more generally, is there a way out there to skip the same number of cells each time when applying a formula. This would allow one to create a tight array of data from a broad sample. We'll call this solved if someone has a tip for that!
|
#4
|
|||
|
|||
Hi,
I couldn't resist that challenge! I attach solution number 4 (hopefully the last) with a general formula. The number 3 in the formula (twice) is number of lines skipped. The number 10 in the formula (twice) is the first row with data to sum. Kind regards Bjorn |
#5
|
|||
|
|||
Wow! What a formula! Ok, this is what I was thinking originally. Its going to take me about 2 hours to dissect this one (have to learn about ADDRESS and INDIRECT fxns) Thanks!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Moving a Long Document | clpix | Word | 1 | 01-14-2010 08:55 PM |
Moving Entire Text | Sleeper | Word | 2 | 01-10-2010 04:40 PM |
Moving from Vista to Windows 7 | zyzzyva57 | Office | 0 | 12-18-2009 04:41 PM |
Range Formula | aleksandr | Excel | 10 | 05-18-2009 12:14 PM |
Moving Items | windsurfer | Outlook | 0 | 12-06-2005 06:36 AM |