Auto-sorting rows & best case scenario

[jchristl]

I have been struggling with a formula that calculates worst case and
best case scenarios for several rows of data. What I have been doing
is manually sorting each row separately ascending, and then
multiplying the highest value with the highest possible solution

ex.

9 4 56 8 2

if this was the 5 entries spread out over 15 different choices, I
would sort this row highest to lowest,

56 9 8 4 2 ... (10 blank entries)....

then multiply them:
x15 x14 x13 x12 x11
840 126 104 48 22

and add them all up = 1140. Theoretically this is the best case for
this particular row of data.

Each week a row is fixated, and every week the next row, left to
right, is fixated. For example, after week 1, the first value of 9 is
forcably fixed in that spot, and is multiplied by 15 (instead of 56).

It would be great If I could get all this done dynamically behind the
scenes without me having to manually Data Sort each row.

Anyone have any ideas for me?

 

[Bernie Deitrick]

j,

For each row, use the array formula (entered using Ctrl-Shift-Enter) - this example is for data in
row 2, columns A to O:

=SUM(IF(COUNT(A2:O2)>=ROW($A$1:$A$15),LARGE(A2:O2,ROW($A$1:$A$15))*(16-ROW($A$1:$A$15))))

which can be copied down for subsequent rows. This solution does not require the data to be sorted.

HTH,
Bernie
MS Excel MVP