Hi,

I'm looking for a formula that averages a selection within a dataset.
However, a user of the sheet should be able to determine which
selection is made. For instance, a user has to be able whether to
average the data matching with column A = x, or column A = y. Moreover,
the formula should take into account that within this selection, another
selection can be made, i.e. average of A = x, B = z. Finally, I'm
desperately looking for a graph which follows this selection, i.e. plot
the retrieved data over the years (average over all cells for which A=x,
B=z, and C(year)=2000, or 2000-2002...). I hope someone can help me...

Thanks,

Peter


--
Peter1999
------------------------------------------------------------------------
Peter1999's Profile: http://www.excelforum.com/member.php...o&userid=33997
View this thread: http://www.excelforum.com/showthread...hreadid=537609

Peter,

I understand that you have 4 columns. First two columns contain some
field values, third is the year, 4th is the amounts to be averaged.
Assuming that a value for A is in K1, a value for B in K2 and hte year
in K3, the following formula will give you the average of the entries
meeting all three criteria.

=SUMPRODUCT(--(A2:A100=K1),--(B2:B100=K2),--(C2:C100=K3),D2:100)/SUMPRODUCT(--(A2:A100=K1),--(B2:B100=K2),--(C2:C100=K3))

If you further want to chart only permissible entries, you can use an
additional column, where the following formula, to be placed in E2, can
be copied down:

=IF((A2=K1)*(B2=K2)*(B2=K3),D2,NA())

You can base your new chart on column E:E and it will only include the
non-#N/A values.

HTH
Kostis Vezerides

Thanks for replying Kostis!
Your solution would indeed work if I were to work work with plain
values, and standard averages. However, the formula should also work
with harmonic means and medians instead of averages and text values in
columns A and B. I was thinking array functions, but still have no clue
how to solve this problem. Maybe a solution is an array with a
concatenate, an average (or harmonic mean or median for that matter,
and an if function). Help anyone?

Thanks a lot in advance,

peter


--
Peter1999
------------------------------------------------------------------------
Peter1999's Profile: http://www.excelforum.com/member.php...o&userid=33997
View this thread: http://www.excelforum.com/showthread...hreadid=537609

Peter,

You are raising several issues. One is that A and B migh contain text.
The formula I suggested does not mind if there is text in either column
(neither C for that matter). It would mind if there is text in column
D, which is the column with the numeric data to be processed
selectively, if I correctly understand your situation.

The harmonic mean will be calculated in exactly the same way, except
that instead of D2:D100 (oops, just spotted a typo in my suggested
formula) you use 1/D2:D100.

SUMPRODUCT is sort of half way between standard and array formulas.
Without CSE it performs what SUM would do WITH CSE. For example, the
suggested formula, as a purely array formula would be implemented as:

=SUM((A2:A100=K1)*(B2:B100=K2)*(C2:C100=K3)*D2:D10 0)/SUM((A2:A100=K1)*(B2:B100=K2)*(C2:C100=K3))

Only now you would use Ctrl+Shift+Enter (CSE).
But it is not necessarily the function of choice for all the
statistical functions.

An alternative would be a dynamic filter, which would produce in a
separate area all the permissible values according to parameters in
cells. Then you could use your statistical functions and chart over the
dynamic data set.

Write back if your wavelength is in any way along the lines of my post.

Regards,

Kostis

Thanks again, Kostis,

The option of a dynamic Filter Range sounds interesting; If I
understand it correctly, it could work like this:

On sheet 1, the user could define the (text) values for A and B, select
a year for C, and Excel would extract those records from sheet 2 (the
actual database) to a seperate sheet (results). That would already help
me a lot!

A few questions remain, besides how to do this ;-). For instance, could
the result sheet automatically sort the records on years? And could it
give the statistics over the numerical value per year on a seperate
field? Could the results also include records for which A is correct
but for which B has not been entered? Thanks again, your help is much
appreciated.

Kind regards,

Peter


--
Peter1999
------------------------------------------------------------------------
Peter1999's Profile: http://www.excelforum.com/member.php...o&userid=33997
View this thread: http://www.excelforum.com/showthread...hreadid=537609