Microsoft Office Forums Moving formula range multiple cells when moving sum over one cell
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
02-05-2010, 11:17 AM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21

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!
Attached Files
 Internet Example.xls (20.0 KB, 23 views)
#2
02-21-2010, 11:38 AM
 BjornS Windows Vista Office 2003 Competent Performer Join Date: Jan 2010 Location: Sweden Posts: 116

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
Attached Files
 Temperatures_by_quarter.xls (41.0 KB, 28 views)
#3
02-25-2010, 05:16 PM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21

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
02-26-2010, 12:26 AM
 BjornS Windows Vista Office 2003 Competent Performer Join Date: Jan 2010 Location: Sweden Posts: 116

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
Attached Files
 Temperatures_by_quarter_General_formula.xls (28.0 KB, 31 views)
#5
02-26-2010, 10:38 AM
 FraserKitchell Windows XP Office 2007 Novice Join Date: Dec 2009 Posts: 21

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!

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post clpix Word 1 01-14-2010 08:55 PM Sleeper Word 2 01-10-2010 04:40 PM zyzzyva57 Office 0 12-18-2009 04:41 PM aleksandr Excel 10 05-18-2009 12:14 PM windsurfer Outlook 0 12-06-2005 06:36 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:10 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft