Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to capture lowest value

0

Hi Variatus,

Thank you for the solution given for Highest and lowest value.

Further, In the attached sheet, I find that the only negative lowest value of the cell is getting capture and not the positive lowest value of the cell. 

I am also looking for positive lowest value of that cell to capture.

Let me explain with below examples: 

1st example, the cell value C5 = 500, than the Max value is capturing as 500 and when the cell value change to C5 = -300, than the Min value is capturing as -300. This example is working fine.

Lets take 2nd example, the cell value C5 = 1000, than the Max value is capturing as 1000, but when the cell value change to C5 = 700, than the Min value is not capturing as 700.

Hope you understand the problem 

Can you please able to help me for the solution. 

Attached file for your perusal.

Regards,

excel
MS Office Version: 2016
posted by: SunilA (rep: 58) on Wed Jun 30, 2021 at 12:43 pm
Answer
Discuss

Discussion

Sunil

Haven't followed your dialogue with Variatus but if your example 2 occurs after example 1 on the same day, wouldn't you want the -300 to be captured (after all, that was the minimum realised that day)?
John_Ru (rep: 6152) Jun 30, '21 at 1:10 pm
Hi John,
Thanks for your inputs.
You view is right from my 1st example. Same I want to convey.
But this both examples are two different individual examples and is not related to each other.
I tried to explain through two set of examples where I am getting results with 1st example, but I am looking for solution like my 2nd example. 
Hope you understand.
Please help for solution. 
Regards
SunilA (rep: 58) Jun 30, '21 at 3:41 pm
Sunil

I think the code from Variatus works (to my understanding). If the minimum for C5 is -300 for a day, F5 should be -300 (even if C5 becomes 300 that day since -300 is the minimum).

His code works correctly e.g if you set C5 to 300 then delete F5, then (after a time delay) F5 is set to 300.

I think the code works but your understanding of mathematical terms seems to differ from mine.

I won't do anything more to fix a problem (which I don't thinlk exists!), sorry.
John_Ru (rep: 6152) Jun 30, '21 at 3:54 pm
Sunil

Just saw your revised question. Again if F5 is deleted, 700 is captured correctly (if the timer is running- is that true for your case?). Nothing to see here (in my opinion)!
John_Ru (rep: 6152) Jun 30, '21 at 4:27 pm
Add to Discussion

Answers

0
Selected Answer

Hi Sunil,

Your questions appear to bring out the worst in me. You have caught me out again. Sorry for wasting your time. The flaw you discovered has the reason explained below, and, if negative values must be considered, it applies to maximum values as well as minima.

When a cell is blank its value is zero. Therefore, a new value of 200 is not smaller than the existing and no change will take place. To overcome this, a special rule must be added to the effect that any new value should replace a blank, and a lower next value to replace only a non-blank value. This condition is now implemented for both Min and Max in the attached workbook.

posted by: Variatus (rep: 4889) on Wed Jun 30, 2021 at 9:18 pm
Discuss

Discussion

Thank you Variatus for understanding the problem and providing the solution at no time.
I feel you are a God of excel, who have every solution to any kind of problem in excel.
I always feel surprise by getting solution for the problem. 
Everytime Teachexcel Team is supportive.
Teachexcel Team always rocks.
Thanks for your support always.
Regards, 
SunilA (rep: 58) Jul 1, '21 at 2:52 am
Thank you for the applause, Sunil.
Variatus (rep: 4889) Jul 1, '21 at 10:51 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login