Solved

Looking for alternative formula for a SUMPRODUCT(OFFSET function

Posted on 2010-11-24
6
600 Views
Last Modified: 2012-05-10
Hi
I have the following formula in Excel 2007 posted in cell M199:
SUMPRODUCT(OFFSET($GJ$197,0,-M$192+1,1,M$192),COHORT!$V12:V12))

Open in new window


....whereby Row197 represents a flipped input, and Row 192 equals the month in my timeline.

The formula works successfully and does what it is supposed to do.  However because of the length of the sumproduct (i.e from column M to GJ) and the number of rows (this goes down a few '000 rows) my spreadsheet is enormous (100mb).  Also it takes some time to recalculate.

I was wondering if anyone could think of an alternative formula, perhaps using dynamic range names, which would eliminate the need for so many SUMPRODUCTS, or at least speed up my spreadsheet?

Many thanks for any help you can offer

Brian
0
Comment
Question by:boru8482
  • 2
  • 2
  • 2
6 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 34208681
Is that the complete formula, you have 2 open brackets and three closed so it won't work as is, is part of the formula missing?

As COHORT!V12:V12 is a single cell the result of the OFFSET must also be a single cell, is that guaranteed?

Could you explain a little more, what's in M192 exactly, are you copy the formula down or across or both?

regards, barry
0
 

Expert Comment

by:bo8482
ID: 34208759
Hi Barry

Well spotted.  There is an IF formula just before it which basically looks at a switch as to whether to bother with this calculation or not - you can forget it about for now.  This is the TRUE part of that IF statement.

Cohort V$12:V12 is dynamic in that if you drag the cells to the right, it will extend all the way to V$12:GT12.  This range is then multiplied within the SUMPRODUCT by the OFFSET part of the formula.  

This formula is also dragged down a few thousand rows so as you can see I have got a lot of SUMPRODUCTs going on causing my s'sheet to run very slowly and get very large.

I was thinking it could be done by having a dynamic range for the COHORT part and the OFFSET part by I'm not sure whether this can a) be done or b) how one goes about it if one can?

As for M192, this is just the number of the month that my timeline refers to.  I.E M192 is month 1, M193 is month 2 (although "month" isn't actually written, just the number).  Hope that helps a little....I appreciate it is hard to explain what I'm trying to do...

Thanks
Brian
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 34209137
OFFSET function is volatile (meaning it re-calculates every time the worksheet is re-calculated - even if none of the inputs have changed)

You could try replacing OFFSET with an INDEX formula

It looks longer but it should be more efficient, try this in place of the OFFSET

=INDEX($A$197:$GJ$197,COLUMNS($A$197:$GJ$197)+1-M$192):$GJ$197

regards, barry

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Expert Comment

by:bo8482
ID: 34209706
Hi Barry

Thanks for this - I will give it a shot first thing in the morning and let you know how I get on.

Cheers
Brian
0
 

Author Comment

by:boru8482
ID: 34212310
Hi Barry

The formula worked!  Many thanks for that...

However, the file size hasn't reduced mainly because of the range of the SUMPRODUCT (i.e. the range it is looking at is stretching from A to GJ).

One of the pieces of information that I omitted to tell you was that this formula is actually copied down in blocks of three (i.e. same formula for c.1000 lines down, then a new block with the same formula 1000 lines down etc).  The only difference between the three blocks is that they refer to different periods of time - i.e  Periods A is 10 months long, B 50 months and C, 24 months).  The length of these periods is changeable though depending on what is input.

What I'm wondering is, would it be possible to get the SUMPRODUCT to work with a range that only goes as far as is needed depending on the length of the period set?  This way, my SUMPRODUCT would only look at a range of 10 months in Period A and not the full 84 periods (which would hopefully reduce my file size?).  I imagine it will need an OFFSET formula within the SUMPRODUCT, which whilst it would increase volatility, would bring file size/calculation speed down which is what I'm hoping ultimately to acheive.  

If you were able to write a formula that does all that I would be very grateful, however I realise you answered the question I originally asked so happy to award the points on that basis.  

Here's what the formula currently looks like:
SUMPRODUCT(INDEX($A$197:$GJ$197,COLUMNS($A$197:$GJ$197)+1-M$192):$GJ$197,COHORT!$V12:V12)

Open in new window


Many thanks
Brian
0
 

Author Closing Comment

by:boru8482
ID: 34212316
Reduced volatility, not quite file size, but happy that this is an improvement on my original formula
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

766 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question