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

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

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))))

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