Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




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
Posted to microsoft.public.excel.worksheet.functions




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 "CMan23" 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
Posted to microsoft.public.excel.worksheet.functions




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 "CMan23" 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
Posted to microsoft.public.excel.worksheet.functions




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 "CMan23" 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 "CMan23" 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 
Display Modes  

