Need help in putting the maximum value in a cell

[nawaab007]

Hi Folks,

Need a small help from your side. I need to populate a cell with the maximum value from a set of values. The values are all the historical values that have been there in the cell. Please help.

So let us say that a cell has value 100 and is then changed to 200 and then to 150. The cell below it should show 200 because it is the maximum from the set {100, 200, 150}.

Please note that the values in the cell are populated by virtue of a formula.

Regards
Nawaab007

 

[Darkspartan]

Are you wanting to do all of this in one cell?

 

[repairman615]

Hello,

Welcome to the board!!

Here is an example for cells A1 and A2. A1 contains your formula cell. A2 is the max of the two and updates whenenver a cell is changed on the entire sheet.

right click sheet tab>>
veiw code>>
paste:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#00007F">If</SPAN> Range("A1").Value > Range("A2").Value <SPAN style="color:#00007F">Then</SPAN> Range("A2").Value = Range("A1").Value<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Change A1 and A2 to suit.>>
Close the VBEditor.

Change a cell anywhere on the sheet.

Does that help??

-Jeff

 

[nawaab007]

Yeah. The values which are modified are in one cell and the value showing the maximum of the historical values are in the cell just below it.

 

[repairman615]

This may have an advantage of being easily modified:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#00007F">Dim</SPAN> Onerng <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> Onerng = Range("A1") <SPAN style="color:#007F00">'''Change here to your cell containing formula, max value _<br>                            between the two will be in the cell directly below.</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> Onerng.Value > Onerng.Offset(1, 0).Value <SPAN style="color:#00007F">Then</SPAN> Onerng.Offset(1, 0).Value = Onerng.Value<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[nawaab007]

Hi Jeff,

Thanks for the prompt response. Is there a way to avoid the usage of macros in this case? I want to do all this without employing macros.

Regards,
Nawaab007

 

[Jonmo1]

You can use a circular reference..though I'm not sure of any odd consequences by doing so...

Click Tools - Options - Calculation
Put a check on Iteration - leave the other 2 boxes at their default values.

Now you can use a formula like this in A2

=IF(A1>A2,A1,A2)

 

[nawaab007]

I have heard circular references should not be used often. Anybody knows the reason?

 

[Jonmo1]

I have heard circular references should not be used often. Anybody knows the reason?

Because the formula will recalculate itself over and over again.
The number of times that you put in the "maximum iterations" box.

So it increases calculation time.


It depends on how many times you intend to do it..
If you're talking 1 or 2, or even say 100 formulas using circular reference, it's probably just fine.
But if you're going to be doing thousands, then it's probably going to be problematic.

 

[nawaab007]

Because the formula will recalculate itself over and over again.
The number of times that you put in the "maximum iterations" box.

So it increases calculation time.


It depends on how many times you intend to do it..
If you're talking 1 or 2, or even say 100 formulas using circular reference, it's probably just fine.
But if you're going to be doing thousands, then it's probably going to be problematic.

Thanks for the information jonmo1. That helped.