CODEWITHSUNDEEP
vbaexcelexcel-formula
user9114427
user9114427

Reputation:

Using offset to create a dynamic range

I currently have a column, which starts at B28 (The title) and from B29 Values.

enter image description here

I add data to the column using a macro, and calculate the mean and standard deviation of the profit figures. Since i add data, I can't just select for example B29:B10000 as it will skew any mean and standard deviation, as it will also take into account blank cells. Therefore, I need to create a range, which will update when data is added and will only range from the first to last numerical figure, no blank cells.

The current formula I use which does not work:

=OFFSET('Panel'!$B$28,1,0,COUNTA('Panel'!$B28:$B999)-1,1)

(also tried selecting the whole column $B:$B but did not work)

Is there any solution for this?

Cheers

Upvotes: 0

Views: 408

Answers (1)

Ames
Ames

Reputation: 470

yes. Convert the range to table.

enter image description here

enter image description here

enter image description here

Use normal formula e.g. from average(B2:B9) to average(tablename[Profit])

Before Year 8 is added

enter image description here

After Year 8 is added - auto update

enter image description here

Upvotes: 1

Related Questions