Help with MAX function

[slimimi]

Hi there - i cant seem to get the MAX function to work.

I typed in Cell E3 "=max(D3)"

In D3 - i have a formula which uses DDE to get prices which constantly change

The results in E3 (using the MAX function - change too, rather than holding the maximum number reached)...

Can anyone help me on this one please?

Thanks in advance.

 

[Jon von der Heyden]

The MAX() function will return the highest number appearing in the specified range.

=MAX(A1:A10)
....will return the highest value appearing between cells A1 and A10.

The formula you have entered below references a single cell, which is therefore the highest value in that specified range.

In order to get the highest price of constantly changing prices, you need to record each new price in a new cell....

Regards
Jon

 

[Aladin Akyurek]

E3:

=IF(D3>RECALL(),D3,RECALL())

Required: The free morefunc.xll add-in.

I believe there is some VBA code for this you might consider using instead.

 

[slimimi]

Thats great - thanks for the responses.

SO how can i get round this issue?

If i go with populating say 3 cells with the lastnumber in the moving price cell - would that work? How would i go about doing that?

Thanks in advance.

 

[Jon von der Heyden]

I'm not familiar with DDE. Is it similar to OLE? How is the data refreshed? I imagine you need a macro to capture the value in D3 each time the data is refreshed - into a new table/array.

Something like:

Sub MakeRng()

Dim LastRow As Long, MyRng As Range

With Sheets("[B]NewSheet[/B]")
    LastRow = .Range("A" & Rows.count).End(xlUp).Row + 1
    
    .Range("A" & LastRow) = Sheets("[B]DataSheet[/B]").Range("D3")
    
    Set MyRng = .Range("A2:A" & LastRow)
End With
    
ActiveWorkbook.Names.Add Name:="MyRng", RefersTo:=MyRng

End Sub

Assumptions / Requirements:
1. You add a new sheet, in this macro called "NewSheet". Change name as you desire.
2. Your data is housed in a sheet called "DatSheet". Change name as desired.
3. You do not already have a workbook level named range called "MyRng".

If your DDE query is refreshed with a macro then you could call this macro at the end of it's procedure.

Your formula in E3 would now read =MAX(MyRng)

HTH
Jon

 

[slimimi]

Thanks everyone - this seems to work:

I use A1 as DDE link cell
I use B1 to display Max number reached in A1

Type in B1 : =MAX(A1,B1)

This seems to work because if i type, say:

8 in Cell A1 now - then B1 will show 8
6 in Cell A1 now - then B1 still shows 8
10 in Cell A1 now - then B1 will now show 10

The really Strange thing is that i cant get this to work the other way round by changing the MAX to MIN.... I am very confused. Anyone know why?

Thanks in advance.

 

[slimimi]

Its ok - i got it - this works.

=IF(A1<>"",MIN(A1,B1))