Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
C-Man23
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
C-Man23
 
Posts: n/a
Default 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!


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"