How to capture highest and lowest value till respective time


Hi Team,

I am looking the high value and low value till respective time. Please refer to attached excel sheet for your reference.

For example, At particular time highest percentage might achieved 105% and lowest percentage might achieved 95% which need to capture and show the same.

Can we capture or record the highest % and Lowest % till respective time.




1. D5 is updated through formula by other databased and it is not manually enter. It is system generated result. Update automatically every second.

The daily work out day time is between 9:15 AM to 3:30 PM.

2. Over every second highest and lowest need to be recorded. 

Yes, the worksheet /workbook will be loaded in the memory during the entire period. 





There are two open questions. Please edit your question to provide the answers. 1. How  and when does the value in D5 change? Is the value entered manually or by some other program? Is there an operator who enters a value in D5 every hour or every other set interval?
2. Over which period must highest and lowest be recorded? A day? A week? A month? Will the worksheet remain loaded in memory during the entire period or is it possible that the workbook will be closed and re-opened later?
Variatus (rep: 4544) Mar 29, '21 at 10:22 am
Add to Discussion



When you start the attached workbook you will be offered the choice between "Yes", "No" and "Cancel".

  • If you press "Cancel" the workbook will open normally, without any action.
  • If you press "No", the Timer will be started.
  • If you press "Yes", the previous Min & Max will be deleted and the Timer will be started.

You can also start the Timer manually by running the procedure "SetTimer". You can stop the Timer by runing the procedure "StopTimer". When you close the workbook the Timer will also be stopped.

The code that does all the above is contained in the two code modules ThisWorkbook and TXL_5303. The latter is a standard code module whose name doesn't matter for the functioning of the code. But if you place the code from ThisWorkbook in another type of module the project won't work as described here.

The Timer will run the code every minute. You can adjust the interval in the procedure SetTimer. The code will take the current value of D5:D7 and change E5:F7 if the new average is either lower or higher than the previous high or low. 

In the sub MyMacro you can enable the workbook to be saved after each update. If you don't you will be asked whether you want to save changes when the workbook is closed.

In the Enum Nws, as the top of the code, you can set the position of the columns. You can change the columns almost as you wish but the Min must be to the right of Max.

I noticed that you didn't set the cell format for E6:E7. Therefore you will see the result of calculations in the wrong format. Read all the comments in the code and ask if there are any you don't understand.



Hi Variatus,
I trying to figure out the solution but unable to get the result.
Can I request you to please help me to understand the excel input and output.
NitilA (rep: 14) Apr 6, '21 at 9:44 am
Hello Nitil,
Always happy to teach, I am. But in this case I don't understand your question. Which input? Which output? If you need more space, create a new question. Attach the file you are talking about and mention the exact cells (by their address and/or marked in the workbook you attach). The more precise your question the more useful will be my answer. Promise.
Variatus (rep: 4544) Apr 6, '21 at 8:10 pm
Add to Discussion

Answer the Question

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