Gary Wood 16:33 23 Apr 2009
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 2009

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 2009

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 2009

There might be a more elegant formula...

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

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

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?