CODEWITHSUNDEEP
excelexcel-formula
VoiceOfUnreason
VoiceOfUnreason

Reputation: 57279

Avoiding duplicate OFFSET calculations

How can I use the results of a single OFFSET() in multiple cells?

I have a worksheet with many rows. For each row, there is an contiguous set of columns that I'm interested in. The range of columns I'm interested in depends on some cells set elsewhere; I can use the OFFSET() function to define the range.

For each row, I need to call several array functions (SUM, AVERAGE, etc) on the same array reference.

This can be done by calling OFFSET in each case... SUM(OFFSET(args)), AVERAGE(OFFSET(args)); but I'm finding errors as I try to keep all of the OFFSET calls in sync with the latest definitions of the array.

Is there a way that I can turn

B1: =SUM(OFFSET(args))
C1: =MAX(OFFSET(args))

into

A1: =OFFSET(args)
B1: =SUM(A1)
C1: =MAX(A1)

or the equivalent, so that I can change the definition of the reference in one place and apply it to all of the functions in the row?

Upvotes: 1

Views: 1039

Answers (3)

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

Reading your example it occurs to me this is a perfect time to employ a named formula. CTRL-F3 to open the name wizard, create a name like MyRange and in the REFERSTO: section, enter the OFFSET() formula you were putting in A1. Be sure to make it an absolute referenced formula.

Then you should be able to sum the resulting range you created with

A1: =SUM(MyRange)

B1: =MAX(MyRange)

Upvotes: 4

user667489
user667489

Reputation: 9569

You could use an OFFSET array-entered formula to generate the correct set of columns for each row, starting in a consistent column to the right of the source table (or on another sheet if necessary) and working to the right. Then you could use the same range in all your other formulas in cells B1, C1 etc.

If you sometimes need to select different numbers of columns, you would need to add some error handling to prevent possible #N/A errors from contaminating your calculations. This can be done via IF combined with ISERROR or ERROR.TYPE.

Upvotes: 0

Tim Mayes
Tim Mayes

Reputation: 574

You could build your range address using the Address function, and then use Indirect to reference that range. For example, in A1 suppose you enter: =ADDRESS(5,2,1,1)&":"&ADDRESS(5,3,1,1). That will give you an address of $B$5:$C$5. Now, you can use a formula such as =SUM(INDIRECT(A1)) to get the sum of the values in that range.

You didn't give enough detail, so I don't know if your use of Offset will fit into this scheme. If you can calculate the beginning and ending rows and columns then you may be able to replace Offset with Address and Indirect. You can probably work it in somehow, but I don't have enough info to help with that.

Upvotes: 1

Related Questions