Formula help for recording Max Min values of anothe cell.

**Trims** · 07-27-2012, 06:43 AM

[B]HI

I wonder if anyone can help with some formatting.

I have DDE data being streamed from MT4.
From that data I have another cell calculating the daily change of Forex pairs.
Which is also displayed as a chart showing daily raise or fall of the various pairs, this gives me a quick overview of what’s happening in the market.
It all works well and I managed to create the sheet and the formulas. I find that as the data is constantly changing it would be very helpful to have the graph show (normal clustered column graph) as a shadow the max or min values of the day, to give me an instant view of the daily events.

I believe to do this I would need to have another 2 columns to show the max value and min value of the changing data in the original cell then add it to the chart. It would show then four bars alongside each other the max min column only changing as the value increases/decreases. This would be my first ideal. Later I would like to format the chart to just show the max/min values as a shadow on the same bars as the original data.

Firstly, could anyone help with the formula form recording the Max Min values of the original data? Also how would I be able to reset this value daily?

Thanks[B]

**Trims** · 07-29-2012, 07:10 AM

After a lot of searching I finally mange to find the answer to my problem.

Thanks to an old post from 2006 from Biff which pointed me in the right direction I manged to get a record of highest and lowest figures of the day, thanks Biff.

So here's what I did:

To record daily highest price in M2 from L2 variable price. =IF(ISERROR(L2),"",IF(L2="","",IF(N2=0,M2,MAX(L2,M2))))
To record daily lowest price in N2 L2 variable price =IF(ISERROR(L2),"",IF(L2="","",IF(N2=0,L2,MIN(L2,N2))))

I made some minor adjustments to what Biff suggested, As At the end of each day I would clear the daily open price column and the figure in the cells recording the data would reset to blank until I entered new daily open price.

So now I can reset and view the market much easier.

I believe I could use IFERROR to cut down on the formula, but thats for another day as i am happy to have it all working for Monday. If anyone could change the code I would be even happier.

here the thread which helped, thanks again to thoses who contributed to it.

http://www.excelforum.com/excel-gene...owest-low.html

Thanks

**Ace_XL** · 07-29-2012, 12:03 PM

If you are using Excel 2010, you can use IFERROR instead of IF(ISERROR

Hence,
=IFERROR(IF(L2="","",IF(N2=0,M2,MAX(L2,M2))),"")
and
=IFERROR(IF(L2="","",IF(N2=0,L2,MIN(L2,N2))),"")

**Trims** · 07-29-2012, 04:02 PM

Thanks for that.

If I wanted it to be used on 2007 or earlier should I continue to use the ISERROR. as I may need to use it on my laptop usiing excel 2003 .