question about using the offset function

[GS Brown]

Is there a way to use the offset function (or another function) as a way
to supply an expanding range to other functions like 'average'? For my
purposes, there may be multiple series of numbers within say a column,
with each 'series' (i.e. set of contiguous rows) separated by a few
blank cells. The objective of the function would be to supply a limited
set of numbers (which can expand) to the function whose result is the
main interest. For example, within column D, I might have rows 5-10
representing adult male fish weights, rows 15-20 representing adult
female fish weights and rows 25-30 representing juvenile fish weights.
The cells between each set of data are blank. I imagine that I'd have a
separate average function for each of the three 'sets' of data using the
offset function as a way to define each set, each of which can expand.

I've tried something like the following to get a result for each set of
data, say the one in rows 5-10:

=average(offset($D$4,1,0,counta($D:$D),1))

I expected that the offset function would return the range beginning in
cell D5 extending down column D until the first blank cell (D11) is
encountered. Instead, the offset function appears to return all of
column D. What am I missing about the offset function? How else can I
specify a range of values that can expand to another function like
average?

I want to avoid the use of Named Ranges because I'd have a million of
them. Well, maybe that's an exaggeration but you'll likely know what I
mean.

Any suggestions will be greatly appreciated.

Thanks in advance,
Gayle

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Earl Kiosterud]

Gayle,

The OFFSET function is returning all the stuff in column D because of
COUNTA($D:$D).

I have a suggestion. If possible, organize your data as a bonafide table.
Have a column for your categories (adult male, adult female, etc). Now put
"Adult male" in each row of the respective rows, "Adult female," etc (same
column). Or use abbreviations.

You'll find all kinds of tools (Autofilter, pivot tables, subtotals, and
more) are now available. You can use a pivot table to get the averages you
want for each category, or the DAVERAGE function. Also, you can have any
number of any particular category. You can sort the table on any column at
any time to arrange the records for a particular need.

 

[GS Brown]

Hi Earl,

Thanks for your suggestion about simpler organization of data. I will
put some thought toward that. You're absolutely right in that
organizing data to take advantage of pivot tables and other Excel
'ready-made' summarizing capabilities is an intelligent way to go.
Please allow me one 'however' to plead my case. However, I have many
(many) small data sets, some of which group naturally on a sheet, as in
my example of fish weights and sometimes, it does seem convenient to
have the data displayed in nciely formatted tables with formulas at the
base of columns or rows that summarize aspects of the data. I'd still
like to try to find a way to supply functions like 'average' with a
limited, defined but periodically expanding range of values. This
quest is also meant to improve my Excel skills.

You pointed out that the reason that offset was returning all of column
D in my example was that I had specifed $D:$D. I figured that was the
reason but I couldn't figure out a way to change to column reference to
restrict what offset returned. Any suggestions?

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[GS Brown]

Hi Earl,

I just solved my problem with the offset function. I also discovered
that whereas my solution will be useful for creating dynamic charts with
multiple data sets on the same sheet, it's not necessary in the example
I gave with the average function. The average function will do
automatic updating as long as you supply it with a range of cells that
is larger than that which you're updating. I feel silly about that
discovery but oh well. I did learn something more about Excel.

-Gayle

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****