Spreadsheet query

  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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

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

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…