Use of AGGREGATE

[Gringoire]

Hi guys,
sorry if my question is dumb but I'm not able to found a solution.
I would like to use AGGREGATE function on rows to carry out separate averages data.

on below example i would like to have:
on D3 the average of Range G:O including only the cells where row (2) value is "h"
on E3 the average of Range G:O including only the cells where row (2) value is "kg"
on F3 the average of Range G:O including only the cells where row (2) value is "€"

I try a lot of different syntax like:
=AGGREGATE(1;0;G3:O3:G2:O2;"h")
=AGGREGATE(1;0;G3:O3*G2:O2="h")
=AGGREGATE(1;0;G3:O3:G2:O2="h")
and so on... but
Of course I alredy read this: WorksheetFunction.Aggregate method (Excel)
and googled a lot but Aggregate is quite complex and I still miss the right formula.

could you please give me a hint?
thanks.

 

[Fluff]

Why not use
=AVERAGEIF($G$1:$O$1,D$2,$G3:$O3)
then fill down & across

 

[Gringoire]

Why not use
=AVERAGEIF($G$1:$O$1,D$2,$G3:$O3)
then fill down & across

Hi Fluff, thanks for the answer.
I do not use AVERAGEIF for several reasons:

- I planned to use the function through VBA where I prepared a Sub() with a lot of optional parameters mostly working on Application.WorksheetFunction.Aggregate() that is already called from several different points of my worksheet code.
- I also have to evaluate StDev, Min, Max, etc on the same ranges: Aggregate should be able to do it just changing the first two parameters.

Unfortunately I'm still missing the correct syntax of Aggregate on the most complex cases.

 

[Fluff]

The only time I use Aggregate with conditions is with the arguments 14 or 15.
If I try to use a condition like you are doing for average I just get #VALUE, so cannot help.

 

[jasonb75]

If you specifically want to use AGGREGATE for this then you will have to speak to microsoft and ask them to re-write the entire function.

As it stands, criteria based arrays can only be used with function numbers 14-19, functions 1-13 will only accept direct arrays.

 

[Fluff]

Never knew that, explains why I couldn't get it to work.

 

[jasonb75]

I think that I have the function numbers correct in my previous post, but like @Fluff I don't use all of them.

To try and explain it a little clearer, when you start typing the formula you get the following tooltips

AGGREGATE(function_num, options, array, [k])
AGGREGATE(function_num, options, ref1, ...)

Only the functions which use a [k] argument permit use of an array, the remaining functions require a range reference (ref1, ...) the same as functions like sumif(s), countif(s), etc. These functions will not accept any kind of array.

Note that the use of anything like (range1=range2) means that the range becomes an array, meaning that it is no longer a valid range reference and will only work with the [k] argument functions.

 

[Gringoire]

Thanks to Fluff and jasonb75 for the help.
After some thinking I decided that the easiest way is to change the output format of my Excel worksheet in order to workaround the need of conditions on Aggregate range.

 

[Fluff]

Glad we could help & thanks for the feedback