Lowest Value

[shamidi]

Hi, this is my first post I'm sorry if this has already been resolved but I could not find it in the forums previous threads.

I am currently creating a spreadsheet that has numbers changing constantly (Think stock market) in the cells. So as the stocks change, some of the cells change values.

Is there a way that I can store the minimum value of the cell into another cell? So for example, if Cell A1 changed values automatically 300 times in one day, the range being between 33-54, is there a way I can get A2 to store the value 33?

Thanks in Advance

PS - I have no problem using VBA as long as there is a way to use it w/o the Worksheet.onChange function b/c that bogs down my system!

[Maistrye]

One of the reasons Worksheet_Change might bog down your system is because you're making changes to the Worksheet from within the Worksheet_Change event. As these changes trigger the Worksheet_Change event... you can end up with a considerable amount of overhead, as it recursively calls itself. Excel eventually detects this (sometimes), and stops the process, but it eats up considerable CPU.

In the future, if you decide to use the worksheet change macro, put the following code around the section where you make changes to your worksheet:

Application.EnableEvents = False
[code to make changes]Please Login or Register to view this content.[/CODE]

As you can see, this is a circular reference. It will need to be an array formula because B1 (where you enter the formula) has an initial value of 0, which can screw things up... ie. it takes 0 as the value, no matter what is in A1.

Maximums are easier, assuming you don't have negatives (that is, if you have negatives, you'll have to set up something like the above to avoid 0 values):

Please Login or Register  to view this content.

As to a VBA solution, I'd be inclined to use the Worksheet_Change macro, but that doesn't meet your specs.

Scott

[shamidi]

wow, excellent. thank you so much. Quick and very useful.

now if i were to use this excel spreadsheet on another computer, would i have to keep making those changes to the min and max iterations?

[Maistrye]

It's sort of tied to the workbook.

There are some weird things to make note of.

The circular references thing will be enabled IFF the first workbook you open has it enabled. It will be disabled IFF the first workbook you open has it disabled.

If you enable it for one worksheet, you're stuck with it for all the ones you have open. If you disable it, again, it affects all open documents. Strangely, it could act like a contagious disease, if you wanted it to.

Essentially, it's a global calculation option.

:-)

Scott