A problem for gurus, I'd say...

[Guest]

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?

 

[Bernie Deitrick]

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

 

[Guest]

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!