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!

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?
 

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("1:"&A1))))}

--
Regards

Roger Govier


"C-Man23" wrote in message
oups.com...
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!

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?
 

Roger Govier wrote:
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("1:"&A1))))}

--
Regards

Roger Govier


"C-Man23" wrote in message

Thanks Roger, You are an Angel! This works perfectly!
oups.com...
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!

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?
 

Hi

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

--
Regards

Roger Govier


"C-Man23" wrote in message
oups.com...

Roger Govier wrote:
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("1:"&A1))))}

--
Regards

Roger Govier


"C-Man23" wrote in message

Thanks Roger, You are an Angel! This works perfectly!
oups.com...
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!