Tough one...

[Peter1999]

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...).

In a best-case scenario, the formula would cut out any extreme values, for example <0 or >100, but I don't even know whether Excel can do that.

I hope someone can help me... thanks a lot in advance


Peter

[vezerid]

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

[Peter1999]

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

[vezerid]

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:D100)/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

[Peter1999]

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