+ Reply to Thread
Results 1 to 2 of 2

Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up?

  1. #1
    C-Man23
    Guest

    Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up?

    Please Help!!!
    I am trying to have excel find the highest x number in a group of
    numbers and sum them up.
    Example:

    Week 3
    156
    222
    172
    185
    158
    178
    166
    185
    133

    I need the Top 3 numbers summed up. (222,185,185) = 592

    Changes Weekly:

    Week 4
    156
    222
    172
    185
    158
    178
    166
    185
    133
    195
    178
    222

    I need the top 4 numbers summed up. (222,222,195,185) = 824

    This will go on for 44 weeks.

    I need excel to pick the highest 3 numbers and sum them up. I cannot
    use the large function because it will pick only 1 number and I need
    the x number cannot change on it's own. I need it to pick 222,185,185
    and sum them up I have a database with over 150+ columns which I will
    have to sort using a macro and then apply the following formula. This
    range will increase weekly and for every 3 numbers added, I will need
    to pick 1 more of the highest numbers and sum them up. I thought I had
    it by finding the x largest number example 4th(using x large formula)
    highest number (185) and making a formula with
    =SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9:C140,">"&C143)))
    though, I found that if there is more than one number of the same
    number, it will error. if i change the > to >=, it will pick too many
    numbers. (C143 has the formula to find the x highest number(determined
    in another cell).
    I hope you understand what I am trying to do. It is very complicated
    to me and I am now officially frustrated. Over 10 hours on this
    (writing macros for sorting, etc.) Please help if you can. Thank You!


  2. #2

    RE: Help! How do you get excel to find the x(changes daily, marked in

    Hi
    You might want to use rank(no,range) this will tell you the rank of the
    number.
    You can then use a sumif to just add up the top x numbers.

    You would need to incorporate a check as if two numbers are the same they
    would have the same ranking, but at least you would know which ones they were
    and could check for them easily

    Paul

    "C-Man23" wrote:

    > Please Help!!!
    > I am trying to have excel find the highest x number in a group of
    > numbers and sum them up.
    > Example:
    >
    > Week 3
    > 156
    > 222
    > 172
    > 185
    > 158
    > 178
    > 166
    > 185
    > 133
    >
    > I need the Top 3 numbers summed up. (222,185,185) = 592
    >
    > Changes Weekly:
    >
    > Week 4
    > 156
    > 222
    > 172
    > 185
    > 158
    > 178
    > 166
    > 185
    > 133
    > 195
    > 178
    > 222
    >
    > I need the top 4 numbers summed up. (222,222,195,185) = 824
    >
    > This will go on for 44 weeks.
    >
    > I need excel to pick the highest 3 numbers and sum them up. I cannot
    > use the large function because it will pick only 1 number and I need
    > the x number cannot change on it's own. I need it to pick 222,185,185
    > and sum them up I have a database with over 150+ columns which I will
    > have to sort using a macro and then apply the following formula. This
    > range will increase weekly and for every 3 numbers added, I will need
    > to pick 1 more of the highest numbers and sum them up. I thought I had
    > it by finding the x largest number example 4th(using x large formula)
    > highest number (185) and making a formula with
    > =SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9:C140,">"&C143)))
    > though, I found that if there is more than one number of the same
    > number, it will error. if i change the > to >=, it will pick too many
    > numbers. (C143 has the formula to find the x highest number(determined
    > in another cell).
    > I hope you understand what I am trying to do. It is very complicated
    > to me and I am now officially frustrated. Over 10 hours on this
    > (writing macros for sorting, etc.) Please help if you can. Thank You!
    >
    >


+ 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