Question about an argument, in an OFFSET dynamic range formula

  • Thread starter Thread starter Terry B.
  • Start date Start date
T

Terry B.

(Recently I posted the question (another ng)):
What's a good formula for calculating a running-sum for column C,
where I don't want the values for cells $C$1:$C$21 to be included, and
where the column is periodically-added onto with no end in sight, a
formula which would give the result for cells C22 and downward as far
as and including the most recent entry.
Gord Dibben was kind enough to share the following:
=OFFSET(Sheet1!$C$22,0,0,COUNT(Sheet1!$C:$C),1)
(the formula was exactly what I was seeking)

MY PRESENT QUESTION IS THIS: How does Excel know, since you ask in
the nested COUNT function to include the entire C column, NOT to
include the first 21 cells?

Thanks in advance ;-}
Terry
 
Terry,

The C22 tells it to start

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi

The general syntax for OFFSET() is
OFFSET(BaseCell,RowsShift,ColumnsShift,HeigthRows,WidthColumns)
With either of first 2 parameters omitted, the starting row number or column
or both of them for returned range are same as for base cell.
With both of last 2 parameters omitted, a single cell reference/value is
returned.
To return a range, both 3th and 4th parameters have to be determined
(parameter value=1 to return single row/column).
P.e. you can modify Gord's formula
=OFFSET(Sheet1!$A$1,21,2,COUNT(Sheet1!$C:$C),1)
and you get exatly same range returned

Now about COUNT()
COUNT() returns count of numeric values in range. In Gord's formula, you get
the single-column range starting with cell C22, and reaching down as much
rows, as much numeric values (0's included) you have in column C. When you
have some numeric values in range C1:C21, there are some empty cells at
bottom of returned range - which doesn't matter for your case. When there
are some empty cells or text values between numeric ones in column C down
from C22, then some values may be excluded from returned range. To check the
returned named range, select Insert.Name.Define, select your named range,
and point anywhere in range formula field - the defined range is activated
(bordered with dash-line) on worksheet.
There are alternative ways to declare 3rd parameter in the formula
=OFFSET(Sheet1!$C$22,,,COUNTIF(Sheet1!$C:$C,"<>")-N,1)
where N is number of non-empty rows in range A1:A21. The formula doesn't
work, when values in counting column (column C at moment) are generated by
formulas. When the last is the case, use
=OFFSET(Sheet1!$C$22,,,COUNTIF(Sheet1!$C:$C,">""")-N,1)
to count all text values, or
=OFFSET(Sheet1!$C$22,,,COUNTIF(Sheet1!$C:$C,">0")-N,1)
to count all numeric values >0 (or combine both of them).
All of these formulas are sensitive for gaps in returned range. When this
may be the case, and your next formulas don't return errors with empty cells
in returned range, you can use the fixed number for 3rd parameters. P.e.
when you are sure, that your data never exceed 1000 rows, you can use the
formula
=OFFSET(Sheet1!$C$22,,,1000,1)
 
Back
Top