# Formula to calculate highest values in a range

1. ## Formula to calculate highest values in a range

Hi Guys,

I need to create a formula that uses several columns BUT only adds the highest value columns (number of columns will be dynamic).
e.g.

B1=7
C1=2
D1=3
X1=4
Y1=5
Z1=6

Normally: A1= B1+C1+D1+X1+Y1+Z1

I need: A1= max values (n) added together
n=number of columns (or number of values) to add together so it is dynamic.
if n=2:
A1=B1+Z1
if n=3:
A1=B1+Z1+Y1
if n=4:
A1=B1+Z1+Y1+X1

So i need a dynamic formula which can find the max values within a range of columns depending on the n value, which specifies how many of the max columns are added together.

I know it sounds weird & complicated but i'm hoping there is a formula of some sorts that i can insert into the cells which calculates this value for me.

If your wondering what the use of this is, it is for calculating the BEST OF marks for exam questions (6 questions, answer 5 and you will be awarded highest 3 marks). There are several exams with variances on this scenario & i need a formula that can cover this.

Any ides?

Regards,

Shuja  Register To Reply

2. Try this,
Select B1:D1
then with the Ctrl key pressed, select X1:Z1
both ranges should now be highlited
at the top menu select insert name define, in the name box enter this

Lrg

then OK
in a different cell enter this formula
=LARGE(Lrg,1)+LARGE(Lrg,2)+LARGE(Lrg,3)  Register To Reply

3. ## Thanks for the solution.

To add a little more complexity to it:

I am assigning the formula to the cells using VBA code. Once the 1st cell has a formula i am then using code to populate the entire column with the same formula (just changing the cell references for each row).

In order to this using the large function you mentioned, i can't use the insert->name-defineName method as this would not work for new rows.

What i would need is the Large to be able to accept single, multiple cell references. eg. Large({A1,B1,C1,Z1},1). This does not work at the moment.
It does work if the cell references are replaced with the actual values (Large({5,6,8,12},1).

But i need it to work using individual cell refernces. Is this possible?

Regards,

Shuja  Register To Reply

4. Perhaps this will work
=SUM(LARGE((B1:D1,X1:Z1),{1,2,3}))  Register To Reply

5. To extend Dave's suggestion a little....

If you have your n number in cell A1, e.g. if A1 contains 3 you want to sum the 3 highest values

=SUMPRODUCT(LARGE((B1:D1,X1:Z1),ROW(INDIRECT("1:"&A1))))  Register To Reply

6. ## Hi Guys,

I need to explain my situation better I'll use the solution that was provided in the earlier replies to this thread.

lrg is a range of cells defined as name:
=LARGE(Lrg,1)+LARGE(Lrg,2)+LARGE(Lrg,3) - this is fine but but i need to replace the range values (lrg) with individual cell references:

=LARGE({B1,C1,D1,X1,Y1,Z1},1)+LARGE({B1,C1,D1,X1,Y1,Z1},2)+LARGE({B1,C1,D1,X1,Y1,Z1},3) - this version does not work as the syntax is incorrect. Is there a way of specifying individual cell references within the LARGE function instead of a Range?

Regards,

Shuja  Register To Reply

7. Originally Posted by Dabooj
Hi Guys,

I need to explain my situation better I'll use the solution that was provided in the earlier replies to this thread.

lrg is a range of cells defined as name:
=LARGE(Lrg,1)+LARGE(Lrg,2)+LARGE(Lrg,3) - this is fine but but i need to replace the range values (lrg) with individual cell references:

=LARGE({B1,C1,D1,X1,Y1,Z1},1)+LARGE({B1,C1,D1,X1,Y1,Z1},2)+LARGE({B1,C1,D1,X1,Y1,Z1},3) - this version does not work as the syntax is incorrect. Is there a way of specifying individual cell references within the LARGE function instead of a Range?

Regards,

Shuja
Did you even try the last 2 solutions? I believe you have your answer.  Register To Reply

8. Oh, I see how it works now.

Thanks - that does solve the problem i had. Much appreciated.

Regards,

Shuja  Register To Reply