0
votes

Looked a bit for this info but I'm stuck.

let's say I have two columns. I have to find top largest values from one column and sum the corresponding row values from the other column.

To find the values and average the result, I'm using the following formula (where c19 is the number of items to retrieve):

AVERAGE(LARGE('3.Tabela_DC_Marca'!B1:B1500;ROW(INDIRECT("1:"&C19))))

What I want now, is to retrieve and sum the matching row values from the second column. I can't seem to find a way to nest that formula.

Something like this?

Sumif Range -

AVERAGE(LARGE('3.Tabela_DC_Marca'!B1:B1500;ROW(INDIRECT("1:"&C19))))

Criteria - ????? Sum_range -

 '3.Tabela_DC_Marca'!C1:C1500;ROW(INDIRECT("1:"&C19)

Can anyone help?

Thank you

1
You can use SUMIFS with >= than the result of LARGE as your criterion.BigBen
Thank you for your answer but I still can't do it. Since I can't press enter here I'll edit my original post if you can then elaborate a bit more.Pedro Rocha

1 Answers

1
votes

Following on from BigBen's comment, something like this?

The formula in D4 is

=SUMIF($B$2:$B$11,">="&LARGE($B$2:$B$11,2),$A$2:$A$11)

and sums the values in A corresponding to the two largest values in B. From this you can easily average them.

enter image description here

It's also possible to use this with INDIRECT, viz enter image description here