AVerage if and help

[Excel15]

Ok so I have a group of multiples that show the current multiple in the blue highlighted cells and to the right are tables showing their history broken out into quartiles. Below this I want to find what the average numbers are based on whichever quartiles the current multiple is sitting in. This isn't real easy to explain, so I'm attaching an example. If you scroll down to row's 95-100 the yellow highlighted cells are where I want to find a calculation that will give me the average SD, Median Performance, Avg. performance and % of time positive for all of the multiples depending on which quartile they are all in. Any help with this would be much appreciated

Cheers!

[southward]

Hi,

I can't see a way to do an AVERAGEIF in such a format.

The best I can come up with is a helper column to use an OFFSET MATCH formula to return the desired value. Then average the values in the helper column.

See attached example.

Hope this is helpful.

Cheers

[FDibbins]

What decides if you use the 3rd or 4th (or any other) QTR? It seems random?