Gary Wood 16:33 23 Apr 09
Locked

I have a spreadsheet of data. I want to put a formula in a column to give me the total of the four highest values in the five preceding cells.

For example (letters are columns):
A=5, B=2, C=6, D=3, E=4

Then column F should = A+C+D+E = 18

But, in the next row, the lowest value might be in column C, in which case, it should be excluded where all the others are totalled.

I hope this makes sense and that someone can tell me the correct formula.

Many thanks,

Gary

VoG II 17:00 23 Apr 09

There might be a more elegant formula but try

=SUM(LARGE(A1:E1,1),LARGE(A1:E1,2),LARGE(A1:E1,3),LARGE(A1:E1,4))

Gary Wood 17:07 23 Apr 09

Thanks, VoGâ„˘.

I'd just found the LARGE function online when you posted back! With what you posted and what I read, I came up with:

=(SUM(LARGE(D10:H10,{1,2,3,4})))

Which does exactly what yours did, I think, but is just a bit shorter.

Thanks very much for your help.

Gary

VoG II 17:08 23 Apr 09

There might be a more elegant formula...

=SUM(A1:E1)-MIN(A1:E1)

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…