Formula Help Please

W

whymj

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!
 
S

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.
 
S

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
 
W

whymj

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top