Help with MAX function

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
May 27, 2008
Messages
532
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
    
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
 
Last edited:

slimimi

Well-known Member
Joined
May 27, 2008
Messages
532
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,309
Members
414,052
Latest member
Dual Showman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top