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?

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


wrote in message
...
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?