Suppressing circular reference warning from OFFSET

[John]

My spreadsheet shows product, its street date, and units sold for each
month for a number of years. After the final month, it calculates
total units sold for the first three months after its street date. So:

Columns, A to AA:
Product, StreetDate, July-98, Aug-98...June-00, Initial_3mo_Units_Sold

Example row, 1:
Some Artist's CD, 7-5-98,-1,1,0,40,....(June-00),41
(Initial_3mo_Units_Sold).

Here's the problem:
Initial_3mo contains a formula with an OFFSET that eventually will try
to count itself. For example, if the StreetDate is 5-1-00, the
initial_3mo will start from June-00 (column Z) and continue to AA1
(which contains the OFFSET formula) and AB1 (which is blank). How do I
restrict the OFFSET to a range that includes only the months (C:Z)?

Here's the formula:
=SUM(OFFSET($C3,0,MATCH($B3,$C$1:$Z$1,1)-1,1,3))

 

[Frank Kabel]

Hi John
one way
=SUM(OFFSET($C3,0,MAX(MATCH($B3,$C$1:$Z$1,1),21)-1,1,3))
though this may lead to wrong results if no units have been sold

another way:
IF(MATCH($B3,$C$1:$Z$1,1)>24,"do nothing",SUM(...))


HTH
Frank

 

[Earl Kiosterud]

John,

Insert two columns between your last data column and the column with the
formula. Hide them.

You're just not lazy enough! I'd turn a big projection TV onto its side
so I could lie down and watch it!

 

[John]

Good suggestion Earl, however, since the number of months that define
how much activity is a variable (could be 12 months initial activity
or 22, etc.), the number of columns inserted and hidden would also be
a variable.

The solution is probably going to involve some sort of MATCH, but I
haven't figured it out yet.