Max/Min

G-fer

Board Regular
Joined
Jul 18, 2005
Messages
192
Hi all .... seasons greetings and a happy new year.

Hope you can help.

Lets say cell A1 contains a number. The number changes throughout the day as a result from an automatic DLL refresh.

I'd like cell A2 to tell me the maximum number in cell A1 during the period that the workbook is open, and A3 to tell me the minimum.

I've tried this in A2, "if(A1>A2,A1,A2)", but as I expected, I got the dreaded circular reference error message.

I guess I need a VB solution.

Regards ..... G-fer.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hello there G-fer,

I don't believe you can do this without VBA. You could use a couple of UDF's like this ...



<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
  
<SPAN style="color:#00007F">Function</SPAN> MinStatic(celRef <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Static</SPAN> tmpValMin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">If</SPAN> tmpValMin < Application.WorksheetFunction.Min(celRef) <SPAN style="color:#00007F">Then</SPAN>
        MinStatic = tmpValMin
    <SPAN style="color:#00007F">Else</SPAN>
        MinStatic = Application.WorksheetFunction.Min(celRef, tmpValMin)
        tmpValMin = MinStatic
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
  
<SPAN style="color:#00007F">Function</SPAN> MaxStatic(celRef <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Static</SPAN> tmpValMax <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">If</SPAN> tmpValMax > Application.WorksheetFunction.Max(celRef) <SPAN style="color:#00007F">Then</SPAN>
        MaxStatic = tmpValMax
    <SPAN style="color:#00007F">Else</SPAN>
        MaxStatic = Application.WorksheetFunction.Max(celRef, tmpValMax)
        tmpValMax = MaxStatic
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>



Call in a cell such as ..

=MinStatic(A1)

=MaxStatic(A1)


HTH
 

G-fer

Board Regular
Joined
Jul 18, 2005
Messages
192
Thanx Zac ... I'll give it a run and let you know how I go.

Regards ... G'fer.

PS. Is it cold over there? .... it's 34c and 88% humidity here.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Nothing to dread about the "circular references error." As Stephen Bullen shows it can actually accomplish exactly what you want! See any of his examples on the subject at http://www.oaltd.co.uk/Excel/SBXLPage.asp#WksFuncs

The only other way to accomplish what you want is with a worksheet_change event procedure (or, depending on circumstances, the less specific _calculate event). IIRC, this code has been posted to this forum and to the XL newsgroups. To search the archives of the latter visit groups.google.com


G-fer said:
Hi all .... seasons greetings and a happy new year.

Hope you can help.

Lets say cell A1 contains a number. The number changes throughout the day as a result from an automatic DLL refresh.

I'd like cell A2 to tell me the maximum number in cell A1 during the period that the workbook is open, and A3 to tell me the minimum.

I've tried this in A2, "if(A1>A2,A1,A2)", but as I expected, I got the dreaded circular reference error message.

I guess I need a VB solution.

Regards ..... G-fer.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,067
Office Version
  1. 2016
Platform
  1. Windows
G-fer said:
Hi all .... seasons greetings and a happy new year.

Hope you can help.

Lets say cell A1 contains a number. The number changes throughout the day as a result from an automatic DLL refresh.

I'd like cell A2 to tell me the maximum number in cell A1 during the period that the workbook is open, and A3 to tell me the minimum.

I've tried this in A2, "if(A1>A2,A1,A2)", but as I expected, I got the dreaded circular reference error message.

I guess I need a VB solution.

Regards ..... G-fer.



Go to Tools >Options >Calculation (tab) > Tick Iteration CheckBox > Set Maximum Iteration to 1 > OK.


Now, you can either enter in A2 your formula or a simpler one like follows :


=Max(A1,A2)
=Min( A1,A2)

Regards.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,539
Messages
5,572,786
Members
412,484
Latest member
deezina07
Top