Excel - How to hold a variable value?

L

Littler

I would like to know what formula is required in order to hold the highest value in a variable value cell

e.g. (a) 1st cell = 100 2nd cell = 100
(b) 1st cell = 80 2nd cell = 10
(c) 1st cell = 120 2nd cell = 120 and so on
 
G

Gord Dibben

Littler

Assuming you want no less than 100 to show in 2nd cell.

A1 holds the value.

B1 =MAX(100,A1)

B1 will return 100 or greater but not less than 100.

Gord Dibben Excel MVP
 
L

Littler

A1 Is a variable value ie. there is no maximum limit, but we wish to record the maximum value even if A1 goes lower.
 
J

JMay

Maximize window before reading...

With your data in A1:B3 like you say;
In Cell C1 enter: =MAX(A1:A3,A1:A3) << Instead of pressing the enter key
(after entering) press

the 3 keys (simultaneously) Control-Shift-Enter.

This action will place the { } around the formula

causing it to be Array-entered.
It will then look like {=MAX(A1:A3,A1:A3) } << these {}'s can't be done
from/with the key board.

Hope this helps,,,
 
M

M Littler

Sorry All, The proposals don't work. I don't think I have explained the problem correctly
Cell A1: Represents a share price (which can go up or down over time
Cell B1: I wish to record the maximum price in A1 (This must not go down in value
Cell C1: I enter the formulae which gives me an 90% value of B1 (This will give a price at which to sell my stock to prevent losing too much money or profit)
 
D

Dave Peterson

You'll need some VBA to keep that maximum in B1.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the codewindow:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim CellWithMax As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Set CellWithMax = Me.Range("b1")

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) _
And IsNumeric(CellWithMax.Value) Then
If .Value > CellWithMax.Value Then
Application.EnableEvents = False
CellWithMax.Value = .Value
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

If you make a typing mistake in A1, you may mess up your value in B1. So keep
an eye open.
 
Top