InLegacy Excel, you could use theOFFSET function.
Both approaches are explained below.
What should you do if new data is being added on an ongoing basis?
The result is a “trimmed” range that only includes data from the used portion of the range.
you’re able to read more about the TRIMRANGE functionhere.
The COUNT function returns the number of numeric values indata.
We subtract E5 to “back up” to the correct cell at the start of the range.
We provide E5 forheight, since we want our final range to bencells tall.
This makes the formula more error-prone sincedataand C5 are disconnected.
Now, as long as the reference todatais correct, the formula will work properly.
One limitation of the formulas above is that they won’t automatically include new data added to the range.
you could create a dynamic named rangewith the OFFSET functionorwith the INDEX function.
Another (simpler) option is touse an Excel Tableto create the range.
The number of rows and columns to return is provided by separaterowsandcolumnsarguments.
Rows and columns can be extracted from the start or end of the given array.
AVERAGE Function
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers.
OFFSET is handy in formulas that require a dynamic range.
COUNT Function
The Excel COUNT function returns a count of values that are numbers.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
