OFFSET function question

[Joseph Spain]

Today, I'm having a small problem that I cannot find a solution for.

If you have time... Here's the basic model in simple terms... The formula
I am seeking will reside in cell C1, and the answer to the problem below
should be 1.

.....A...B...C
1...9...1...1
2...4...5...
3...9...1...
4...4...6...

Explanation:

I am attempting to find the largest two numbers in range A1:A4 (which are
9 and 9 in my example). Then, I need to average the *corresponding* cells
in column B. In my example, the values I want to average are located in
B1 and B3 because they are located on the same rows as the two largest
numbers in range A1:A4.

I can use the formula...

{=AVERAGE(LARGE($A$1:$A$4,ROW(INDIRECT("1:2"))))}

....to locate and average the largest two numbers in range A1:A4, but when
I try to use OFFSET to average the adjacent cells in column B, my
formula...

{=AVERAGE(LARGE(OFFSET($A$1:$A$4,0,1),ROW(INDIRECT("1:2"))))}

....averages the largest two numbers in range B1:B4, which does not provide
me with what I require.

Does anyone see how I can receive the correct solution from column B? The
formula will reside in cell C1 and the answer should be 1.

Thanks very kindly.

Best Regards,
Joseph

 

[Ken Wright]

Assuming your numbers are as follows, which numbers fall into your TWO
largest criteria

.....A...B...C
1...9...1...1
2...4...5...
3...9...1...
4...4...6...
5...9...3...

 

[Joseph Spain]

Assuming your numbers are as follows, which numbers fall into your TWO
largest criteria

....A...B...C
1...9...1...1
2...4...5...
3...9...1...
4...4...6...
5...9...3...

Ken, for range A1-A4, the two largest numbers are 9 and 9 (at A1 and A3,
respectively, in this case). I can average those without a problem, but
next, I need to average the cells adjacent to A1 and A3, which are 1 and 1
in this case (at B1 and B3, respectively, in this case).

In other words, I need to find the two largest numbers in a range in
column A, and then average the cells in column B that are adjacent to the
two largest numbers in column A (B1 and B3 in my example).

Thanks much.

Joseph

 

[Ken Wright]

But note the additional numbers I added and clarify what the result would
be, or, clarify that it is not possible to have more than two cells equal
the TOP TWO numbers, eg

what if you had 1,9,2,8,8 - which are the top two?

At the moment your question lacks clarity wrt possible outcomes, so we need
to nail down what is and isn't possible with your data. This will affect
any answers you are given.

 

[Joseph Spain]

Assuming your numbers are as follows, which numbers fall into your TWO
largest criteria

....A...B...C
1...9...1...1
2...4...5...
3...9...1...
4...4...6...
5...9...3...

Ken, please disregard my previous message. I failed to realize you added
a 5th row to my example. I understand what you were asking now. I would
prefer to have a solution for averaging only the two cells that correspond
to the "two 9s" that Excel picks for the largest two numbers.

But for grins, and if you wouldn't mind showing me, how you could find the
average of B1, B3, and B5 for a case like yours above, and based on the
largest two values in column A?

Best regards,
Joseph

 

[Jason Morin]

Ken is asking what should the formula do if 3 or more
numbers tie for the highest number. For example, if A1:A3
= 9 and A4 = 2, do you want to average B1 and B2, or B1
and B3, or B2 and B3? Or all 3?

What if there is a tie for the 2nd highest? For example,
if A1 = 9, A2 = 7, A3 = 2, A4 = 7, do you average B1 and
B2, or B1 and B4? Or all 3?

Jason

 

[Joseph Spain]

Ken is asking what should the formula do if 3 or more
numbers tie for the highest number. For example, if A1:A3
= 9 and A4 = 2, do you want to average B1 and B2, or B1
and B3, or B2 and B3? Or all 3?

What if there is a tie for the 2nd highest? For example,
if A1 = 9, A2 = 7, A3 = 2, A4 = 7, do you average B1 and
B2, or B1 and B4? Or all 3?

In a tie case, whichever two values that Excel idetifies as the largest
will be acceptable, but seeing the solution for all values in the case of
a tie would also be useful.

Thanks for all the work you guys do. You answer about 99% of my Excel
questions without ever knowing it. I rarely have to post to find answers,
but I couldn't find a solution for this one. Thanks again to all of you
who post here daily.

Best Regards,
Joseph

 

[Ken Wright]

The following will average all values in Col B that are equal to the top two
largest values in Col A. that could be anywhere from 2 values in Col A to
all the values in Col A.

=AVERAGE(IF($A$1:$A$100=LARGE($A$1:$A$100,{1,2}),$B$1:$B$100))

array entered using CTRL+SHIFT+ENTER

This will NOT just pick the first two values that happen to be equal to the
top two and just use those (unless there are only two), eg

A B
2 3
4 4
6 2
3 7
6 3
5 8

6, 6, and 5 are equal to the top two largest, so it will average 2,7,3 and
give 4

 

[Joseph Spain]

The following will average all values in Col B that are equal to the top
two
largest values in Col A. that could be anywhere from 2 values in Col A
to
all the values in Col A.

=AVERAGE(IF($A$1:$A$100=LARGE($A$1:$A$100,{1,2}),$B$1:$B$100))

array entered using CTRL+SHIFT+ENTER

This will NOT just pick the first two values that happen to be equal to
the
top two and just use those (unless there are only two), eg

A B
2 3
4 4
6 2
3 7
6 3
5 8

6, 6, and 5 are equal to the top two largest, so it will average 2,7,3
and
give 4

Thanks very much, Ken. I think this will work, but I have another
question... Can the portion of your formula that reads {1,2}be modified
to return the values in column B that correspond to a greater number of
LARGE values in column A?

Joseph

 

[Aladin Akyurek]

Ken,

Shoudn't that be:

=AVERAGE(IF(A2:A6>=LARGE(A2:A6,C1),B2:B6))

with C1 housing 2, the Top N largest criterion?

 

[Joseph Spain]

Shoudn't that be:

=AVERAGE(IF(A2:A6>=LARGE(A2:A6,C1),B2:B6))

with C1 housing 2, the Top N largest criterion?

This works perfectly. I can define variable largest criterion now.

You guys are awesome. Thanks to you and Ken both.

Best Regards,
Joseph

 

[Ken Wright]

LOL - serves me right for staying up late, watching a film and playing on
this damn thing too - Cheers Aladin.