Excel - How to hold a variable value?

[Guest]

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

 

[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

 

[Guest]

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

 

[Alan Cocks]

Assuming your column of figures starts at A1
Put the formula:
=MAX(A$1:A2)
in B2 and autofill the cells below it.

The formula could be expanded to:
=IF(A2="","",MAX(A$1:A2))
to prevent you getting results in "B" when there is no figure in "A"

Alan

I would like to know what formula is required in order to hold the highest

value in a variable value cell.

 

[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,,,

 

[Guest]

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)

 

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