# Max/Min

#### G-fer

##### Board Regular
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.

### 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
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
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
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
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.

Replies
5
Views
72
Replies
3
Views
84
Replies
1
Views
409
Replies
1
Views
80
Replies
3
Views
180