Recording a maximum value in Excel 1

[ekrouse]

I am trying to record a maximum cell value in an MS Excel which is continually dynamically updated.

Note: This is not a request about using the "Max" or Maxa" functions.

My spreadsheet automatically updates with stock market data several times a second. I have a cell which calculates the value of a portfolio during the day. I would like a way of recording in another cell the maximum value that reaches during the day and another cell recording the minimum value it reaches.

Lotus 1-2-3 used to have a function that would do that.

I think some kind of macro would work, but I am not a VB programmer so I would need to have specific instructions to make it work.

Thanks,

ekrouse

 

[combo]

Why notmax/min functions? When you allow circular references (iteration settings) you can in B1: =MAX(A1;B1).
You need to reset values at startup (maybe IF with global condition?).

combo

 

[ekrouse]

Thanks Combo. Your Solution Works GREAT! (and simple too. My favorite kind of solution).

-ekrouse

 

[GlennUK]

Good stuff combo, I like your thinking.

====> *

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[combo]

Thanks!

combo

 

[ekrouse]

Does anyone have another solution than user "combo's" solution? I am using the circular reference approach which works more or less, but does weird things requiring me to switch the "iterations" function (Tools>options>calculations) on and off from time to time. It also doesn't keep the values when the spreadsheet is reopened.

-ekrouse

 

[kbklaus]

A simple question:
If you found a comfortable solution within Lotus 1-2-3, why don't you use 1-2-3 for making that job?

I still am of the opinion, that 1-2-3 is much easier to handle concerning macros (script recording by cursor eg, creation of menues...) and much easier understandable functions compared with EXCEL.

 

[ekrouse]

Thanks kbklaus, but using Lotus 1-2-3 is not an option since the spreadsheet has realtime links to stock market data through Reuters. That is not supported with Lotus 123.

 

[combo]

If I could still propose something, if you do not like to build VBA application, you can use custom function:

Function maxx(v)
Dim r As Range
' Set r = Application.Caller up to 2000
Set r = Application.ThisCell ' since xp
If Val(r.ID) < v Then r.ID = v
maxx = Val(r.ID)
End Function

In case of non-US international settings (esp. decimal separator) additional translation number - string (ID) - number will be necessary.
ID is not stored in saved workbook.

combo

 

[ekrouse]

Thanks Combo,

I think I like this VBA approach better although I will have to read up some on writing custom functions. What part of the code you provided allows for "relative cell reference" rather than hard coded. I assume "r.ID" is the referenced cell address like "B3". How does it remain relative so that it always references the cell above the one where the function is placed (i.e. if the function is put in cells B4, C4 & D4, how does each reference cell B3,C3 & D3?

Sorry to be so basic on this but I'm real squissy on VBA.

Thanks,

-Eric

 

[combo]

No addresses here. The problem is that UDF can't change (most of) worksheet. Range's ID is one of exceptions.
Application.Caller (or newer Application.ThisCell) returns the cell where the calling UDF is. Its ID is the string to use in UDF.

So, suppose A1 stores variable data, formula in B1: =maxx(A1). Application.Caller here is 'B1'.
When you open the workbook, ID of [B1] is "". after recalculation it will start to keep whatever you like. It will disappear when you save workbook.


combo

 

[ekrouse]

Combo,

OK, I think I figured part of it out. Please correct me if I'm wrong. Maxx(v)is a function called "Maxx" with the cell reference "(v)". That is how it knows what cells to reference. I assume a more complex formula which references two cells would be written somthing like "function Maxx(last,prior) where "last" where using the function would look something like "=maxx(b3,d4)".

-ekrouse

 

[combo]

Actually, v is the value you pass as an argument. No address, just current value of market data in your case. After some processing result value is returned as in all worksheet functions.
The reference to the cell where the function is used (Caller, ThisCell) only to avoid the mess in stored data. ID keeps maximum, this value, depending on entry data, can be updated and is returned by this function.

You can easilly build VBA application and store data in arrays, that are recalculated when worksheet is updated. The question is how to store previous value and avoid circular references using UDFs, the above is one of possible solutions .

combo

 

[ekrouse]

Now I need a Minimum Value Formula, but have run into a snag. First off... the formula for Maximum Value worked great since the variable is set to zero each time the spreadsheet is opened. I call it HighWaterMark and it is written:

Function HighWaterMark(CurrentValue)
Dim h As Range
' Set h = Application.Caller up to 2000
Set h = Application.ThisCell ' since xp
If Val(h.ID) < CurrentValue Then h.ID = CurrentValue
HighWaterMark = Val(h.ID)
End Function

The problem is with my LowWaterMark macro...

Function LowWaterMark(CurrentValue)
Dim l As Range
' Set l = Application.Caller up to 2000
Set l = Application.ThisCell ' since xp
If Val(l.ID) > CurrentValue Then l.ID = CurrentValue
LowWaterMark = Val(l.ID)
End Function

Since the macro initializes to "0" when the spreadsheet is opened, it never records the low value unless the referenced cell ("CurrentValue") drops below zero! How do I fix this? How do I initialize the Macro so that HighWaterMark starts at -10000000 and LowWaterMark Initializes at 10000000? Then they should record the correct number as long as they fall inside that range. I tried coding the macro with l = 100000000 but get a debug error.

Also, once this is fixed is there a way to re-initialize the variable while the spreadsheet is open (like a button that runs a macro to "reset" the variable?

Thanks Combo or anyone else for your assistance.

-ekrouse

 

[combo]

First of all, you could analyse how the above code works:
- the ID property can store any text,
- ID is not saved, so it is an empty string ("") if you do not initialise it,
- the code above uses the simplest way to convert stored string to value (Val function).

Basing on this you could extend testing (If (Val(l.ID) > CurrentValue Or i.ID="") Then l.ID = CurrentValue). You can also define any other procedure of the ID string.

combo

 

[ekrouse]

THANKS COMBO!

Your solution works great! I used the...

If L.ID = "" Then L.ID = 10000000.

If the number every approaches that level I'll raise the initialization number.

-ekrouse