how to find the lowest positive value on a constantly changing cell

Did you want A2 reset each day?



Right Click on the sheet name at the bottom of excel and select view code.

Paste this code into the module that opens and close it.

Please Login or Register  to view this content.

With Date reset:-

Please Login or Register  to view this content.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

If I may suggest an alternative to VBA, I might approach this using circular references with iteration enabled. Your problem sounds similar to this discussion a couple of months ago (https://www.excelforum.com/excel-pro...-possible.html ). Setting up the spreadsheet:

1) A1 is your changing cell
2) A2: Enter 1E9 (or other suitably large positive number) (This step is often not needed for MAX() functions -- is that why you say this won't work for MIN() is you forgot to initialize the loop?)
3) A2: =MIN(A1,A2)

That should be enough to do what you want to do. As I explain in other threads discussing iterative calculations, I don't like to leave a circular reference without some way to "reset the loop". Your OP implies that A1 will never be negative, so I might edit A2 to something like =IF(A2<0,1E9,MIN(A1,A2)). Think through what to put in the condition argument so that you get the right kind of check there so the function can reset appropriately. You can also expand the idea to include other reset conditions to handle whatever situations you need to include when testing for reset.

"Couldn't get it to work" is not a very helpful result (for you or me). What did you try? What is it doing or not doing?

Your picture shows it returning 0. Off the top of my head, two scenarios yield a 0 result.
1) You have not yet enabled iteration, so Excel is returning 0 to indicate that it cannot resolve the circular reference.
2) You neglected step 2, enter 1E9 in I2 before entering the formula. If I2 is blank before entering the formula, the Excel processes the formula:
2a) Blank is interpreted as 0, so Is 0<0 (FALSE)
2b) Since 0 is not less than 0, evaluate the value_if_false argument MIN(0,2500), which is 0. If J2 is never less than 0, then the formula will always return 0.

Solution to 1 is to turn iteration on, go to step 2 to enter a large number in I2, then enter the formula in I2. Solution to 2 is to either initialize the loop by entering a suitable value in I2 before entering the formula, or thinking through the logic of the formula (in particular the "condition to test" argument), and see if there is a better choice for that test condition.

Glad that worked. I recommend before you declare this finished that you think through that some more, maybe test it with the full range of possible inputs. If a negative number is ever inadvertently entered into I2 (if it is even possible for I2 to receive a negative value), for example, that value will be the minimum value, and will get stuck there until you do something to it. Be sure to think through the full range of possible scenarios and make sure you know what will happen in those scenarios.