Excel Problem (mostly solved)

-AKIRA-

Hey guys, I haven't posted to the PA forums for quite a while, but my google-fu is failing and I need a bit of help. I figured this was as good a place as any to get assistance.

I've got a rather large excel document I've created for a school project. I have a collection of numbers that I need to keep sorted in ascending order, but these numbers can change at various times. I've managed to get the sort function in Excel to cooperate and sort these numbers, but I couldn't figure out a way to KEEP them sorted, so I wrote a quick macro that sorts them once.

Now I've gotten to the point where I need to run the solver but the solver needs to call the macro each time in order to keep these numbers sorted. How can I get the solver to call a macro? Every time I google it, I come up with a hundred threads with the same posts that treat calling a macro with solver as a forgone conclusion.

If there's a better way to keep these numbers sorted while solver runs, please let me know.

Thanks.

-AKIRA-

Maybe there's a way around some of this.

The list is only four values long. I merely need to be able to pick each number based on relative value. Obviously the min() and max() function are sufficient for the smallest and largest numbers, but not for the other two. If there's a simple way to pick out these other two numbers without a 8000x nested if statement, let me know. The largest difficulty, however, is that these numbers can be of the same value, so I can't figure out a simple formula to snatch out the 'middle' numbers.

If there's a simple way to grab the address of the minimum number I could exclude it from the min() array to grab the second smallest number, so that would solve my problem just as well.

Thanks.

ecco the dolphin

Have you considered using the small() and large() functions?

-AKIRA-

That's a nifty function that I didn't know about. The only problem here is if the is a repeated number in the data set. If the set is something like [3,3,4,5] I would need to be able somehow know that the three is repeated as small(array,2) yields 4 and not 3 again. Maybe I can play with it for a bit and work something out though. Thanks.

edit: Shit, no it doesn't, not at all. Awesome, I think you just solved my problem, FUCKING AWESOME!

...I love you

-AKIRA-

Well now solver refuses to change my decision variables and declares anything I put in as seed values the best solution when it clearly is not. You wouldn't happen to be a solver guru Eco, would you?

ecco the dolphin

Sorry, no. Can't help you there I'm afraid. =/