Excel formula question

[Speedlock]

Imagine five columns filled with numbers from 0-100. In the sixth column, I need to be able to calculate the average of the two highest numbers in the row.

For example:

A1 B1 C1 D1 E1 F1
56 86 29 89 23 87.5*

* This cell would show 87.5 as the result because the two highest numbers are 86 and 89 so the average is 87.5.

I have had a think about it but I can't think of a suitable way of doing it...can anyone help please?

Cheers!

[destro404]

Just figured this out in a couple of minutes, there might be a better way but this will work. Paste into F1 and fill down as necessary....

=AVERAGE((LARGE(A2:E2,1)),(LARGE(A2:E2,2)))

[Speedlock]

That is superb...but I've just realised that the cells that I need to find the two largest numbers from are not all adjacent so I will not be able to specify a range of cells. Is there another solution to this problem?

[destro404]

Probably.

If you can post a sample excel file so I can actually see it then I'll try and figure something out.

[Speedlock]

Ok, unfortunately I can't attach the real thing but I have mocked up an example of the sort of thing I am talking about.

I appreciate your help!

[destro404]

It works OK using the same basic functions you just need to specify a more complicated array....

=AVERAGE(LARGE((B2:D2,G2:H2,L2,N2),1), LARGE((B2:D2,G2:H2,L2,N2),2))

If you are construction the formulas yourself then it can be easier to put the two LARGE functions in their own cells first to make sure they are working. Then if you want to streamline it paste them into a new AVERAGE formula.

[Argyll]

Dunno if this is any good to you?

http://www.megaupload.com/?d=LDJVHGZ6

[Speedlock]

That is great, thanks and K+ to you both!