# Help with MAX function

#### slimimi

##### Well-known Member
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?

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
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

Last edited:

##### MrExcel MVP
E3:

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

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

#### slimimi

##### Well-known Member
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?

#### Jon von der Heyden

##### MrExcel MVP, Moderator

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:
Code:
``````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

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.

HTH
Jon

Last edited:

#### slimimi

##### Well-known Member
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?

#### slimimi

##### Well-known Member
Its ok - i got it - this works.

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,087
Messages
5,835,302
Members
430,351
Latest member
ddalton

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back