Dynamic Arrays

Define a Dynamic Range Sized by a Cell Value (TAKE & OFFSET)

Choose the first N rows of a fixed revenue range with TAKE or OFFSET, enforcing an integer count and complete numeric data before computing totals and averages.

Choose a cumulative prefix, not the latest rolling window

B2:B7 holds January through June revenue. D2 selects the first N rows starting at B2. A value of 3 therefore selects January–March; it does not select the latest three months.

TAKE returns a slice of the source array. OFFSET returns a reference starting at B2 with height D2 and width 1. OFFSET is volatile in Excel; this example makes no claim about identical recalculation scheduling in Arte Sheets.

The guards require a numeric integer from 1 through 6 and a numeric revenue entry for every selected period. Missing or text revenue is reported instead of being silently omitted from an average. Zero and negative numeric revenue remain valid. Unselected rows do not affect this validation.

Total of the first N periods

The original three-period total is 45000; five periods total 80000.

=IF(COUNT(D2)<>1,"Enter numeric period count",IF(OR(D2<1,D2>ROWS(B2:B7),D2<>INT(D2)),"Invalid Period Count",IF(COUNT(TAKE(B2:B7,D2))<>D2,"Selected periods require numeric revenue",SUM(TAKE(B2:B7,D2)))))

Average through an OFFSET reference

The same input guards protect OFFSET from invalid heights. Keep the result unrounded and format it for display. The three-period average is 15000; five periods average 16000.

=IF(COUNT(D2)<>1,"Enter numeric period count",IF(OR(D2<1,D2>ROWS(B2:B7),D2<>INT(D2)),"Invalid Period Count",IF(COUNT(TAKE(B2:B7,D2))<>D2,"Selected periods require numeric revenue",AVERAGE(OFFSET(B2,0,0,D2,1)))))

Try the example

Use the original values or change them to test your understanding.