Dynamic Row reference in formula

[VanMn]

This is probably very simple, but I have tried combinations as well as looking up other posts and I haven’t figured it out yet.

I have several sum-product formulas summing vertically from the first row to the last row in a coulmn. The number of rows will be ever growing. When I put a range of B1:B25000, it takes so long to calculate so I wanted to shorten the length of the range. Since I will have varying amount of columns also, I didn’t think that creating a dynamic range in each column was practical. What I did was create a dynamic last row called “LastRow”. Using the formula ="b"&ROW(LastRow) will return the address of the column and last row. What I can’t seem to get is how to use this in a range. For a simple example, summing the contents of column “B”. I have tried SUM(B1&":"&"B"&ROW(LastRow)) and SUM(D1:"D"&ROW(LastRow)), but neither work. Am I going at this completely wrong, or is there a way to use the range name in this way?

 

[PaddyD]

you need indirect() for that sort of formulation. however, it's not liklely to help re efficiency, as the number of calculaitons will probably be the same. if you can sort the data, you would get better performance by limiting the formulas to the relevant, sorted range.

 

[bjwillingham]

Is your dynamic last row simply a named row that will move down as the data range has rows inserted into it?

Using the row(lastrow) formula you could create names using the offset function. For instance if the top cell is named "first" you could refereince the column's date by firstffset(first,row(lastrow)-row(first),0)

You could name a range call 'data1' and have it refer to firstffset(first,row(lastrow)-row(first),0)

Test the actual range definition by hitting F5 and typing in the name. I alwasy screw up that row-row thing. Should I add 1 or subtract 1 or nothing?

 

[Aladin Akyurek]

This is probably very simple, but I have tried combinations as well as looking up other posts and I haven’t figured it out yet.

I have several sum-product formulas summing vertically from the first row to the last row in a coulmn. The number of rows will be ever growing. When I put a range of B1:B25000, it takes so long to calculate so I wanted to shorten the length of the range. Since I will have varying amount of columns also, I didn’t think that creating a dynamic range in each column was practical. What I did was create a dynamic last row called “LastRow”. Using the formula ="b"&ROW(LastRow) will return the address of the column and last row. What I can’t seem to get is how to use this in a range. For a simple example, summing the contents of column “B”. I have tried SUM(B1&":"&"B"&ROW(LastRow)) and SUM(D1:"D"&ROW(LastRow)), but neither work. Am I going at this completely wrong, or is there a way to use the range name in this way?

This rises a few questions. What is the current SumProduct formula that you seem to have in thousands of cells? And, what is the definition of LastRow?

 

[VanMn]

The dynamic "LastRow" is a named row that will move down as the data range has rows inserted into it. Its formula is_
=OFFSET($A$1,COUNTA($A:$A)-1,0,1,150) where up to 150 columns will be available to be called on.

I don't have thousands of rows of data yet, but I was building it for that possibility. I have shortened the range requirement and have seen a speed up, so that is why I pursued this. The example of one formula is SUMPRODUCT((Data!$C5:$C25000=B3)*(Data!$B5:$B25000=B2)*(Data!$G5:$G25000+Data!$H5:$H25000)). I realize I can lower the range now and go back and increase it as the need arises, but I am concerned about being made aware of then the rows have reached the limit of what ever the range callout is. Am I wasting time trying to save time?

 

[Aladin Akyurek]

The dynamic "LastRow" is a named row that will move down as the data range has rows inserted into it. Its formula is_
=OFFSET($A$1,COUNTA($A:$A)-1,0,1,150) where up to 150 columns will be available to be called on.

I don't have thousands of rows of data yet, but I was building it for that possibility. I have shortened the range requirement and have seen a speed up, so that is why I pursued this. The example of one formula is SUMPRODUCT((Data!$C5:$C25000=B3)*(Data!$B5:$B25000=B2)*(Data!$G5:$G25000+Data!$H5:$H25000)). I realize I can lower the range now and go back and increase it as the need arises, but I am concerned about being made aware of then the rows have reached the limit of what ever the range callout is. Am I wasting time trying to save time?

The name LastRow made me to expect something different. What you have is a 150 columns table which can expand/crimp downwards (that is, vertically). And, it's not related to data in Data, but that's a minor point.

That said, I have other questions before proceeding further:

Which column of data in Data is the most complete and of numeric type?

Is the data sorted on some column? If so, which column is it and what is its data type?

Which columns are referred to most frequently by your SumProduct formulas?

 

[VanMn]

I can see where my example might be confusing. I was going to use this method in other formulas as well, some formulas being index formulas and so on. But to your question Aladin, The sheet named "Data!", columns B and C are text columns, and columns G and H are numeric and referred to the most often, with column G being the most complete. Column A is the sort column and is a date format. As I said earlier, if practical I would use this concept in other formulas that referred to this data, not just the Sum Product ones. Thanks for your responses and your time. This is something I can live with, but get interested about learning new techniques

 

[Aladin Akyurek]

I can see where my example might be confusing. I was going to use this method in other formulas as well, some formulas being index formulas and so on. But to your question Aladin, The sheet named "Data!", columns B and C are text columns, and columns G and H are numeric and referred to the most often, with column G being the most complete. Column A is the sort column and is a date format. As I said earlier, if practical I would use this concept in other formulas that referred to this data, not just the Sum Product ones. Thanks for your responses and your time. This is something I can live with, but get interested about learning new techniques

First of all, two definitions:

Activate Insert|Name|Define.
Enter BigNum in the Names in Workbook box.
Enter the following in the Refers to box:

9.99999999999999E+307

Click Add.

Enter Lrow in the Names in Workbook box.
Enter the following in the Refers to box:

=MATCH(BigNum,Data!$G:$G)

Click OK.

If you'd like to have a name, say DTable, that would cover all of your data, you just need to enter in the Refers to box:

=Data!$A$5:INDEX(Data$ET:$ET,Lrow)

Intermezzo: This is one use of Lrow. Another would be for example:

[1]

=MAX(Data!$A$5:INDEX(Data!$A:$A,Lrow))

How can we access a particular column of DTable?

INDEX(DTable,0,1)

would give you the current used range in column A on Data. So:

[2]

=MAX(INDEX(DTable,0,1))

is identical to [1].

Efficiency issue:

SumProduct formulas (and control+shift+entered (so-called array/matrix formulas) are expensive (cause performance degradation) if used in lots of cells and applied to huge ranges (of e.g., 25,000 cells).

What are the possible remedies:

(a) Switch to other means of processing like pivot tables, formulas with database functions;

(b) Apply such formulas to relevant subranges within the range of interest. This requires that the data is sorted in ascending order on some relevant field/attribute. You stated that your data is sorted on column A which houses dates. If SumProduct formulas include date conditions, you can make such formulas to apply to subranges by computing the subrange in relation to the date range. As is clear, this is something you should exploit.

(c) Switch from SumProduct/Array formula to SumIf formulas by creating an additional column(s) using concatenation.

If you had a formula like:

=SUMPRODUCT(--(Data!$C5:$C25000=B3),(Data!$B5:$B25000=B2),Data!$G5:$G25000)

used in lots of cells, you can concatenate C and B values in column EU using

=C5&CHAR(127)&B5

and then use:

=SUMIF(Data!EU:EU,B3&CHAR(127)&B2,Data!G:G)

or expand the definition of DTable with column EU, then use:

=SUMIF(INDEX(DTable,0,151),B3&CHAR(127)&B2,INDEX(DTable,0,7))

 

[VanMn]

Thank you Aladin. The index formula was the key to what I was looking for. Thanks also for the suggestions on a more efficient layout of the table. That is what I appreciate about this forum and the people who use it. It not only gives answers, but an opportunity to learn from each other. Thanks to all who responded.
VanMn