Formula Help Please

[Guest]

I need help setting up a formula to look at a range of data (will vary
between 4, 5, or 6 values), choose the 4 highest values and add them together
(ignoring the rest).

ie.
b1 = 25
b2 = 18
b3 = 21
b4 = 23
b5 = 22
b6 = 25
Total = 95 (ignoring 18 & 21)

or:
b1 = 25
b2 = 18
b3 = 21
b4 = 23
b5 = 22
Total = 91 (ignoring 18)

Thanks!

 

[Guest]

One way:
=SUMPRODUCT(--(LARGE(B1:B6,{1,2,3,4})))

 

[SGT Buckeye]

I need help setting up a formula to look at a range of data (will vary
between 4, 5, or 6 values), choose the 4 highest values and add them together
(ignoring the rest).

ie.
b1 = 25
b2 = 18
b3 = 21
b4 = 23
b5 = 22
b6 = 25
Total = 95 (ignoring 18 & 21)

or:
b1 = 25
b2 = 18
b3 = 21
b4 = 23
b5 = 22
Total = 91 (ignoring 18)

Thanks!

Try this.

=AVERAGE(LARGE(b1:b6,{1;2;3;4}))

This is an array so use control + shift + enter.

 

[SGT Buckeye]

I need help setting up a formula to look at a range of data (will vary
between 4, 5, or 6 values), choose the 4 highest values and add them together
(ignoring the rest).

ie.
b1 = 25
b2 = 18
b3 = 21
b4 = 23
b5 = 22
b6 = 25
Total = 95 (ignoring 18 & 21)

or:
b1 = 25
b2 = 18
b3 = 21
b4 = 23
b5 = 22
Total = 91 (ignoring 18)

Thanks!

Try this,

=SUM(LARGE(Range,{1;2;3;4}))

This is an array so enter wuth Control + Shift + Enter

 

[Guest]

That is exactly what I needed! Thank you for your help!

 

[Guest]

I got an error message when trying this formula . . . I was probably doing
the Ctrl Shift Enter wrong. Thanks for your response!

 

[Max]

welcome. thanks for feedback.