Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A problem for gurus, I'd say...
Assume four columns - A, B, C, D, filled with values (double prec. format);
assume a row (R50), which shows a min() value for each of the columns; assume another row (R51), which shows a sum() of the four values in R50; assume another row (R52) which, via index() and match(), shows the row positions of the data values that generate the result in R51; generally - what I'm searching this way is the smallest possible aggregate of four values, one from each column.. The problem: sometimes the values at coordinates shown in R52 are not 'valid' (the data values are parameters of objects and only special objects can be put together) and in that case, I need to retreive the 'next smallest aggregate' following the one in R51. I think of an algo: (((each A sum each B) sum each C) sum each D) = we have a set of all possible sums; sort this set ascending; pick first, pick second, ....; but: 1.) how to do this?? 2.)for 50 values in each column this would generate a huge set of values; can you do in Excel like - hold this set in memory and only give my worksheet the results? 3.)even if I get to picking the e.g. five smallest sums of the set, how do I find out the row coordinates of the values that generated this sum? Can someone please help me to solve this beast of a problem? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A problem for gurus, I'd say...
Mac,
The best solution is to pick the values that you pass to the MIN function. For example: =MIN(A2:A10,A13:A15,A20:A30,A41:A49) where you only include the 'special objects'. (The example above skips cells A11:A12, A16:A19, and A31:A40) To find the row where the value is found, use =MATCH(A50,A2:A49,False) HTH, Bernie MS Excel MVP "Mac" wrote in message ... Assume four columns - A, B, C, D, filled with values (double prec. format); assume a row (R50), which shows a min() value for each of the columns; assume another row (R51), which shows a sum() of the four values in R50; assume another row (R52) which, via index() and match(), shows the row positions of the data values that generate the result in R51; generally - what I'm searching this way is the smallest possible aggregate of four values, one from each column.. The problem: sometimes the values at coordinates shown in R52 are not 'valid' (the data values are parameters of objects and only special objects can be put together) and in that case, I need to retreive the 'next smallest aggregate' following the one in R51. I think of an algo: (((each A sum each B) sum each C) sum each D) = we have a set of all possible sums; sort this set ascending; pick first, pick second, ....; but: 1.) how to do this?? 2.)for 50 values in each column this would generate a huge set of values; can you do in Excel like - hold this set in memory and only give my worksheet the results? 3.)even if I get to picking the e.g. five smallest sums of the set, how do I find out the row coordinates of the values that generated this sum? Can someone please help me to solve this beast of a problem? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A problem for gurus, I'd say...
The problem is that I don't know which are the 'special' ones unitl I see sum
of the MIN's..so I have to include all of them. But if you are willing to help, plese check my other post in the 'Programming' thread, there's more sepcific discussion of this problem. Thank you! "Bernie Deitrick" wrote: Mac, The best solution is to pick the values that you pass to the MIN function. For example: =MIN(A2:A10,A13:A15,A20:A30,A41:A49) where you only include the 'special objects'. (The example above skips cells A11:A12, A16:A19, and A31:A40) To find the row where the value is found, use =MATCH(A50,A2:A49,False) HTH, Bernie MS Excel MVP "Mac" wrote in message ... Assume four columns - A, B, C, D, filled with values (double prec. format); assume a row (R50), which shows a min() value for each of the columns; assume another row (R51), which shows a sum() of the four values in R50; assume another row (R52) which, via index() and match(), shows the row positions of the data values that generate the result in R51; generally - what I'm searching this way is the smallest possible aggregate of four values, one from each column.. The problem: sometimes the values at coordinates shown in R52 are not 'valid' (the data values are parameters of objects and only special objects can be put together) and in that case, I need to retreive the 'next smallest aggregate' following the one in R51. I think of an algo: (((each A sum each B) sum each C) sum each D) = we have a set of all possible sums; sort this set ascending; pick first, pick second, ....; but: 1.) how to do this?? 2.)for 50 values in each column this would generate a huge set of values; can you do in Excel like - hold this set in memory and only give my worksheet the results? 3.)even if I get to picking the e.g. five smallest sums of the set, how do I find out the row coordinates of the values that generated this sum? Can someone please help me to solve this beast of a problem? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Gurus-Please Help! | Excel Discussion (Misc queries) | |||
Chart Gurus--Please Help | Charts and Charting in Excel | |||
Excel VBA Gurus needed | Excel Discussion (Misc queries) | |||
Should be an easy one for you Gurus... | New Users to Excel | |||
For the Excel Query Gurus | Excel Worksheet Functions |