Table Min. & Max. during calculation cycle

[Brian F]

I have a table, 2 columns by 10 rows (A1:B10). The table values are the result of calculations (imagine they are random). These calculations are performed repetitively for some specified number of repetitions (let's say 10 iterations). (e.g. all of the table values change with each iteration until the 10 iterations are complete. With each iteration all of the values in the table change). At the completion of the 10 iterations (one full cycle) I want to know the minimum and maximum calculate value as the 10 iteration cycle was performed.

To simplify; the table changes 10 times in one cycle. I want to know the minimum and maximum values attained in the cycle.

It is easy to determine the minimum and maximum values of the table for each iteration. It seems the problem would be write some vba code that 1) found the min. and max. 2) save that value 3) find the min and max in the next iteration and compare the old and new values 4) retain the value or replace the min and max values with new values and 5) proceed to the next iteration until the cycle is complete.

I know it's a lot but any help would be greatly appreciate!

 

[Sal Paradise]

How are the values changing? If you have VBCode to run the iterations (like a for loop that goes 10 times) just create two values: lngMin and lngMax

Set both values to 0.

In the loop, just add a simple statement:

    lngMin = WorksheetFunction.Min(Range("A1:B10"),lngMin)
    lngMax = WorksheetFunction.Max(Range("A1:B10"),lngMax)

Basically, "Take the minimum of A1:B10 or the current Minimum, and store it as the minimum. Do the opposite for the max."

At the end, those variables would hold the minimum for that cycle.

 

[Brian F]

Yes, there is a loop that runs the iteration.

Thank you for your help. I need to think about your advice for a bit, I'm not as up to speed as I'd like on vba, and although I seem to figure it out it takes me a bit. You sound confident so I'm betting it will work.

Thank you again.

 

[Sal Paradise]

Basically, you have a loop that runs 10 times.

Each time you go through the loop, you want to do the following:
- Figure out what the minimum number in this iteration is
- Compare it to the current minimum for all iterations in this set of 10 so far
- Store whatever number is the minimum to compare to the next iteration

To do that, first you set a variable (lngMin) to equal a number higher than any minimum you could get (I assumed it would be negative, so I said zero. Use 1,000,000 or something higher if your number won't be negative).

Then in the loop, you add code that says, "Take the minimum of the current iteration and the variable storing my current minimum, and turn that into my new minimum."

You do the same thing for max. When you finish the loop of 10 iterations, you will have a variable that has the lowest/highest number for all 10 iterations.