Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-05-2010, 11:17 AM
FraserKitchell FraserKitchell is offline Moving formula range multiple cells when moving sum over one cell Windows XP Moving formula range multiple cells when moving sum over one cell Office 2007
Novice
Moving formula range multiple cells when moving sum over one cell
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

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
File Type: xls Internet Example.xls (20.0 KB, 25 views)
Reply With Quote
  #2  
Old 02-21-2010, 11:38 AM
BjornS BjornS is offline Moving formula range multiple cells when moving sum over one cell Windows Vista Moving formula range multiple cells when moving sum over one cell Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
File Type: xls Temperatures_by_quarter.xls (41.0 KB, 30 views)
Reply With Quote
  #3  
Old 02-25-2010, 05:16 PM
FraserKitchell FraserKitchell is offline Moving formula range multiple cells when moving sum over one cell Windows XP Moving formula range multiple cells when moving sum over one cell Office 2007
Novice
Moving formula range multiple cells when moving sum over one cell
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

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!
Reply With Quote
  #4  
Old 02-26-2010, 12:26 AM
BjornS BjornS is offline Moving formula range multiple cells when moving sum over one cell Windows Vista Moving formula range multiple cells when moving sum over one cell Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
File Type: xls Temperatures_by_quarter_General_formula.xls (28.0 KB, 31 views)
Reply With Quote
  #5  
Old 02-26-2010, 10:38 AM
FraserKitchell FraserKitchell is offline Moving formula range multiple cells when moving sum over one cell Windows XP Moving formula range multiple cells when moving sum over one cell Office 2007
Novice
Moving formula range multiple cells when moving sum over one cell
 
Join Date: Dec 2009
Posts: 21
FraserKitchell is on a distinguished road
Default

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!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving formula range multiple cells when moving sum over one cell 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
Moving formula range multiple cells when moving sum over one cell Range Formula aleksandr Excel 10 05-18-2009 12:14 PM
Moving Items windsurfer Outlook 0 12-06-2005 06:36 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:28 PM.


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