Improve company productivity with a Business Account.Sign Up

x
?
Solved

Use calculated row number in a sumproduct reference

Posted on 2016-09-14
11
Medium Priority
?
168 Views
Last Modified: 2016-09-19
I have a vary awkward data source from which I need to calculate 888 averages that use a sumproduct formulas making it painfully slow. I would therefore like to at least limit the size of the array but each time the data is updated the number of rows and columns change. It is easy enough to use a formula to calculate the bottom row number and right-most column number but how would I reference those in my sumproduct? E.g. say cell A1 contains the number of rows and c1 contains the number of columns. How would I modify the following formula to change row 2000 to A1 and column LY to cell C1 contents?
=SUMPRODUCT((MasterData!$F$1:$LY$1=B$6)*(MasterData!$A$6:$A$2000=SiteId&TEXT($A8,"00"))*MasterData!$F$6:$LY$2000)/SUMPRODUCT((MasterData!$F$1:$LY$1=B$6)*(
0
Comment
Question by:Rob4077
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 48

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 1000 total points
ID: 41799155
Use the OFFSET function to return a resized range for use in your SUMPRODUCT formula...

   
MasterData!$A$6:$A$2000

Open in new window


....becomes....

   
OFFSET(MasterData!$A$6, 0, 0,  $A$1)

Open in new window


...and...

   
MasterData!$F$6:$LY$2000

Open in new window


...becomes...

   
OFFSET(MasterData!$F$6, 0, 0,  , $C$1)

Open in new window


Note though that the OFFSET function is a volatile function and will recalculate whenever any cell in the worksheet recalculates, whether a precedent cell changes or not. This will have the effect of slowing down the calculation even further.
0
 

Author Comment

by:Rob4077
ID: 41799177
Thanks for the answer and for the warning. I guess that just means that the nature of my problem changes, but it won't be resolved so I might as well not bother. The frustration is that my 888 calculations take about 25 seconds to complete - not a very friendly solution.
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 41799184
You can easily achieve what you need by using either of the following

1- concert your data set to Excel tables and then inside formulas use tables structured references , excel tables has the feature of dynamic range meaning that when you data expands or collapses it will take these expanded and collapsed data into count inside formulas . to convert range to table click anywhere in the table then press control + T , to learn how the structured reference of tables works refer to https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

2- is using dynamic named ranges , this one you would use if you do not want tables , while using option 1 is recommended. Creating named range using Index and match, Not offset then using the dynamic ranges inside the formula , if you want to use this option let me know, so that I give you example of formula
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41799192
Tables are a good option, but INDEX is also a volatile function and whether it is used in the formula or named range still needs calculating.
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 41799201
Index as indirect dependent will not trigger recalculation and in the example I referred using  INDEX as the second part of a range reference will only flag for calculating when the workbook is opened.
0
 

Author Comment

by:Rob4077
ID: 41799449
Thanks for your comments. If it helps I have included a print of a small sample of the data I am working with. Row1 is date
Row 2 are column headings
Column A is the state
Column B is the locationId
Column C is the hour of sale (1= midnight to 1AM, etc)
Other columns contain the data

What I need to do is allow user to select a LocationId (from a drop down in a cell
The result needs to display the data in a format similar to the second attachment.
This requires that I work out what week number (1-6) in the month the date falls into and then display the average for each hour within each day averaged by day within week number.
I can get it to work with SumProduct but I am looking for a faster solution.
Untitled.jpg
Untitled2.jpg
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 41799630
is it possible to upload a dummy file, instead of screenshot?
0
 
LVL 9

Expert Comment

by:Koen
ID: 41800471
I have never been able to use table references in sumproduct formulas, so I don't think that will help...
they do expand the range automatically and even copy the formulas down to added records.
0
 

Author Comment

by:Rob4077
ID: 41800849
Ok, I have create a dummy file with full current functionality. Data is extracted from another computer system and dumped into a spreadsheet. The users doing that are not tech savvy so I need to keep it very basic - no modifications to make it work. That spreadsheet is simply copied and pasted into the MasterData spreadsheet in the attached workbook starting at cell B2. Row 1 and Column A are left unchanged and serve as helper cells to make data lookups easier. I need to allow more rows and columns than appear in the report because the report columns grow every month and the number of rows can also change as locations are added and removed.

The main sheet used is Monthly and the user uses the yellow cells to select the desired location and period. That one works quite acceptably.

The problem worksheet is YTD. No selections are necessary here but it needs to display the YTD Averages. I have yet to double-check that the calculations are correct but I want to get the formulas working acceptably first. When loaded with the latest data this sheet takes up to 25 seconds on my i5 laptop to calculate.
24HourMicroDemo.xlsx
0
 
LVL 25

Accepted Solution

by:
Ejgil Hedegaard earned 1000 total points
ID: 41802332
MasterData looks like a report from an ERP system, so each type of SiteID & Time, like 8456801, will only occur in one row.
If that is correct, then it is not needed to calculate for all 2000 rows, only the one that match.

Index (without column specification) can get all the values in the matched row, and only the values in that row.
INDEX(MasterData!$F$6:$LY$2001,MATCH(SiteId&TEXT($A8,"00"),MasterData!$A$6:$A$2001,0),)

The formula for B8 is
=SUMPRODUCT((MasterData!$F$1:$LY$1=B$6)*INDEX(MasterData!$F$6:$LY$2001,MATCH(SiteId&TEXT($A8,"00"),MasterData!$A$6:$A$2001,0),))/SUMPRODUCT((MasterData!$F$1:$LY$1=B$6)*1)

Then the calculation is fast.
24HourMicroDemo.xlsx
0
 

Author Comment

by:Rob4077
ID: 41804428
Thanks to all of you for your posts. The real solution I needed was the one offered by Ejgil - worked perfectly - Thanks very much.
However I am going to split the points with Wayne because Wayne technically answered the question I asked - it was my fault that I asked the wrong question.
Thanks again one and all
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

579 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