• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To capture Highest and lowest value

Techtrend

Member
Hello Excel Team

Need to capture the highest and lowest value either through a conditional formula or through VBA,The sample doc is attached for reference

The H column is open interest will be dynamic and the value keeps changing as the sheet is updated live for Ex currently the H2 value is 79000 if the value increases to 82000 in H2 col the value in column I2 increases to 82000 capturing the highest value and from there if the value drops to 74000 in H2 column the lowest value has to capture in J2,Now the J2 value will change to 74000 which is the lowest

Let me know if i am clear or needs further clarification
Narendra Kumar
 

Attachments

Techtrend

Member
Thanks for the efforts,But doesnt help

As mentioned The H Column is Dynamic and updated from server every min and the value keeps changing,from that column we need to generate the Highest and lowest value how do we do It dynamically in I and J Col
 

Luke M

Excel Ninja
That's....what happens. o_O

The Sheet_Change event detects when you make a change to col H. If you do, the formulas in the helper columns are constants checking for new min/max values, and they get copied over to col I and J. I suggest you re-open the file I posted and play around with changing the values in col H.
 

Techtrend

Member
Hello Luke

I and J col was only given as an Ex,
As this will be live sheet which will be updated live there will only one Open int col which is the H col and from H Values we need to capture the highest and lowest value for the Day.

I think this can be achieved through VBA,Can you please help me on the same.
 

Techtrend

Member
The sheet which you have sent ,If you see the value in H2 is 33000,
The values will be dynamic and keeps changing in this col,
Now in H2 if it increases to 50000 the I2 will change to 50000 as this the highest and the H2 if the value changes to 40000,
The I2 will be 50000 (Highest OI)
The j2 will be 33000 (Lowest OI)

Please let me know if i am clear
 

NARAYANK991

Excel Ninja
Hi Narendra ,

All of what you have posted was clear in your first post.

What is not clear is your comment that it is not working.

Have you changed values in column H and seen that nothing is happening in I2 or J2 ? If yes , how did you change the values in column H ? Can we repeat the same at our end ?

Narayan
 

Techtrend

Member
In your sheet if i change the value in H ,for Ex in H2 i change to 4000 the j2 also changes to 4000,but when i change the h2 again to 15000 the j2 again also changes to 15000,

The j2 as to remain 4000 as it is the lowest value in H2,similar is with High OI in I2
 

Techtrend

Member
Superb the lowest is working fine,The j2 is fine

Now in the same sheet the value in H2 is 33000 and if it increases to 40000 the I2 will increase to 40000 as the highest value and if H2 drops from 40000 to 4000,the value in I2 will be 40000 as it his the highest.

The highest and lowest logic has to work Parallel in I and J col
 

Luke M

Excel Ninja
It's somewhat annoying when we work hard to provide answers for people, and they don't bother to see if they work. :mad:
If file starts like this:
upload_2014-12-5_10-0-24.png
And then col H changes to a new low...voila, col J updated!
upload_2014-12-5_10-0-41.png
If col H changes to a new max, col I gets updated
upload_2014-12-5_10-1-9.png

So far OP, you've stated that the change is happening in col H, and you want some type of result displayed in col I and J. Both Narayan's and my files do that. As Narayan suggested, please provide better detail as to "what is not working" if the images here have misinterpreted your desired effect.
 

Techtrend

Member
Luke i am sorry,Yes it works fine.

Mr.Narayan,Thanks a Ton it works,
I am new to excel can you please explain what have you done,
How can i change the rows and Col need to apply this to my sheet how can i do it.

Thanks Again
Narendra
 

NARAYANK991

Excel Ninja
Hi Narendra ,

I have defined a named range OI , which is dynamic so that in case your data extends beyond its present range , this will consider the additional data automatically.

Its definition is :

=Sheet1!$H$2:INDEX(Sheet1!$H:$H,COUNT(Sheet1!$H:$H)+1)

In case your OI data is in some other column , just change all references to column H to your actual column ; if however , your data does not start from row 2 , more changes will be required.

To take care of this column change , the code does not have to be changed in any way.

If however your cells which store the maximum and minimum change from I2 / J2 , you need to make the appropriate changes in the code.

Narayan
 

Techtrend

Member
Mr.Narayan

Can you please tell me where can i find this definition in the excel

=Sheet1!$H$2:INDEX(Sheet1!$H:$H,COUNT(Sheet1!$H:$H)+1)

Also one more change the high low of the options has to be calculated daily,
So in the morning when we open the sheet can you take the default value which is in H as the highest and then based on that we then cal the high and the low.

Narendra
 

NARAYANK991

Excel Ninja
Hi Narendra ,

The definition of named ranges is accessed using the Name Manager ; can you see this button on the Formulas tab ?

Regarding your second point , what I suggest is that when ever the workbook is opened , the code will check the date in a stored cell with the system date ; if the two are different , then the code transfers the lowest value in column H into the HIGHPOINT cell , and the highest value in column H into the LOWPOINT cell.

Thereafter , when ever you update your data , the realtime high and low values should get recorded correctly. Is this acceptable ?

Narayan
 

Techtrend

Member
Thanks for the clarification Mr.Narayan
Will apply the same to my sheet will get back to you for further assistance

Thank you so much
Narendra
 

Techtrend

Member
Mr.Narayan

Just wanted to know if this can be simplified,
As the ref of high and low is only from one ref box.

If we can use an conditional formula and lock the high and the low value.

Narendra
 

Techtrend

Member
Mr.Narayan

Looking for a single cell formula (possible an array formula) whereby we can determine highest and Lowest value, you have already done it Using VBA and the name manager .Just wanted to know if we can keep it more simple

1) As you can see the prev attached file the H Col as all the Options data and it is dynamic it will be increasing and decreasing during the day,During this event can we just capture or lock the HIgh and low values during the day.

Just wanted to thank you for your time and Inputs
Narendra
 

Nidhi Naik

New Member
Hi, Mr. Narayan..
Your post was very much helpful but i want that if i have value in H2 only and values will get change in that cell only dynamically..now what changes i need to make in the name range?
can anyone help me?
thank u..
Nidhi
 

taftaf7878

New Member
Hi Mr Narayan,

Your codes work perfectly. However, it will not work if I use a reuters function that uploads values dynamically. In this case, I used =RtGet("IDN";"EUR=";"BID") in that cell. What could be the possible reasons? Please advise. Thanks,
 

Attachments

Top