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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top