software

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,quot;gt;quot;amp;C143)))
though, I found that if there is more than one number of the same
number, it will error. if i change the gt; to gt;=, 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!Hi

You could use an array formula. Commit with Ctrl Shift Enter when
entering for the first time, or modifying.
Do not type the curly braces { } Excel will enter these for you when
you use Ctrl Shift Enter

Using a cell at the top of the column of data to determine the number of
values to be taken. In my example, cell A1 would hold either 3 or 4

{=SUM(LARGE(A2:A1000,ROW(INDIRECT(quot;1:quot;amp;A1))))}

--
Regards

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

Roger Govier wrote:
gt; Hi
gt;
gt; You could use an array formula. Commit with Ctrl Shift Enter when
gt; entering for the first time, or modifying.
gt; Do not type the curly braces { } Excel will enter these for you when
gt; you use Ctrl Shift Enter
gt;
gt; Using a cell at the top of the column of data to determine the number of
gt; values to be taken. In my example, cell A1 would hold either 3 or 4
gt;
gt; {=SUM(LARGE(A2:A1000,ROW(INDIRECT(quot;1:quot;amp;A1))))}
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;C-Man23quot; gt; wrote in message

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

Thanks for the feedback. Glad it all worked for you.

--
Regards

Roger Govierquot;C-Man23quot; gt; wrote in message oups.com...
gt;
gt; Roger Govier wrote:
gt;gt; Hi
gt;gt;
gt;gt; You could use an array formula. Commit with Ctrl Shift Enter when
gt;gt; entering for the first time, or modifying.
gt;gt; Do not type the curly braces { } Excel will enter these for you
gt;gt; when
gt;gt; you use Ctrl Shift Enter
gt;gt;
gt;gt; Using a cell at the top of the column of data to determine the number
gt;gt; of
gt;gt; values to be taken. In my example, cell A1 would hold either 3 or 4
gt;gt;
gt;gt; {=SUM(LARGE(A2:A1000,ROW(INDIRECT(quot;1:quot;amp;A1))))}
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;gt;
gt;gt;
gt;gt; quot;C-Man23quot; gt; wrote in message
gt;
gt; Thanks Roger, You are an Angel! This works perfectly!
gt;gt; oups.com...
gt;gt; gt; Please Help!!!
gt;gt; gt; I am trying to have excel find the highest x number in a group of
gt;gt; gt; numbers and sum them up.
gt;gt; gt; Example:
gt;gt; gt;
gt;gt; gt; Week 3
gt;gt; gt; 156
gt;gt; gt; 222
gt;gt; gt; 172
gt;gt; gt; 185
gt;gt; gt; 158
gt;gt; gt; 178
gt;gt; gt; 166
gt;gt; gt; 185
gt;gt; gt; 133
gt;gt; gt;
gt;gt; gt; I need the Top 3 numbers summed up. (222,185,185) = 592
gt;gt; gt;
gt;gt; gt; Changes Weekly:
gt;gt; gt;
gt;gt; gt; Week 4
gt;gt; gt; 156
gt;gt; gt; 222
gt;gt; gt; 172
gt;gt; gt; 185
gt;gt; gt; 158
gt;gt; gt; 178
gt;gt; gt; 166
gt;gt; gt; 185
gt;gt; gt; 133
gt;gt; gt; 195
gt;gt; gt; 178
gt;gt; gt; 222
gt;gt; gt;
gt;gt; gt; I need the top 4 numbers summed up. (222,222,195,185) = 824
gt;gt; gt;
gt;gt; gt; This will go on for 44 weeks.
gt;gt; gt;
gt;gt; gt; I need excel to pick the highest 3 numbers and sum them up. I
gt;gt; gt; cannot
gt;gt; gt; use the large function because it will pick only 1 number and I
gt;gt; gt; need
gt;gt; gt; the x number cannot change on it's own. I need it to pick
gt;gt; gt; 222,185,185
gt;gt; gt; and sum them up I have a database with over 150 columns which I
gt;gt; gt; will
gt;gt; gt; have to sort using a macro and then apply the following formula.
gt;gt; gt; This
gt;gt; gt; range will increase weekly and for every 3 numbers added, I will
gt;gt; gt; need
gt;gt; gt; to pick 1 more of the highest numbers and sum them up. I thought I
gt;gt; gt; had
gt;gt; gt; it by finding the x largest number example 4th(using x large
gt;gt; gt; formula)
gt;gt; gt; highest number (185) and making a formula with
gt;gt; gt; =SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9: C140,quot;gt;quot;amp;C143)))
gt;gt; gt; though, I found that if there is more than one number of the same
gt;gt; gt; number, it will error. if i change the gt; to gt;=, it will pick too
gt;gt; gt; many
gt;gt; gt; numbers. (C143 has the formula to find the x highest
gt;gt; gt; number(determined
gt;gt; gt; in another cell).
gt;gt; gt; I hope you understand what I am trying to do. It is very
gt;gt; gt; complicated
gt;gt; gt; to me and I am now officially frustrated. Over 10 hours on this
gt;gt; gt; (writing macros for sorting, etc.) Please help if you can. Thank
gt;gt; gt; You!
gt;gt; gt;
gt;