+ Reply to Thread
Results 1 to 8 of 8

Formula to calculate highest values in a range

  1. #1
    Registered User
    Join Date
    11-03-2006
    Location
    Birmingham, UK
    Posts
    18

    Exclamation 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

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    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)

  3. #3
    Registered User
    Join Date
    11-03-2006
    Location
    Birmingham, UK
    Posts
    18

    Exclamation

    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

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Perhaps this will work
    =SUM(LARGE((B1:D1,X1:Z1),{1,2,3}))

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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))))

  6. #6
    Registered User
    Join Date
    11-03-2006
    Location
    Birmingham, UK
    Posts
    18

    Exclamation

    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

  7. #7
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Quote 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.

  8. #8
    Registered User
    Join Date
    11-03-2006
    Location
    Birmingham, UK
    Posts
    18
    Oh, I see how it works now.

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

    Regards,

    Shuja

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1