NEW HIGH INDICATOR FORMULA

[R J ALDRIDGE]

Hi ALL
I would like an Excel formula to indicate at each point when the balance in a column is at a new high level? Say column A has the account balance then let the indicator be in column B at each point there is a new high say in column A? Hoping someone can supply a formula to do this?.
Regards, Robert

 

[PaddyD]

hi - welcome to the board!

this the sort of thing you mean?

Book1
	A	B	C	D
1	9	Largestsofar,oraninstanceofthelargestvaluesofarseen
2	6
3	9	Largestsofar,oraninstanceofthelargestvaluesofarseen
4	11	Largestsofar,oraninstanceofthelargestvaluesofarseen
5	2
6	8
7	4
8	19	Largestsofar,oraninstanceofthelargestvaluesofarseen
9	10
10	20	Largestsofar,oraninstanceofthelargestvaluesofarseen
Sheet1

the formula is:

=IF(A1=MAX($A$1:A1),"Largest so far, or an instance of the largest value so far seen","")

...note the mix of absolute & relative references

 

[R J ALDRIDGE]

Hi to Respondant,
I mean a formula that would show the new high reached at each point not just the all time high?
Regards, Robert

 

[fairwinds]

How about changing Paddys formula to:

=IF(A1=MAX($A$1:A1),A1,"")

 

[R J ALDRIDGE]

Hi Fairwinds and PaddyD,
Thanks for your reponce to my request.
The Formula works fine. Could one of explain all parts of the formula please? as I am new to Excel formulas.
Regards, Robert

 

[tactps]

=IF(A3=MAX($A$1:A3),A3,"")

In English:

if the number in cell A3 if the biggest value (max) in the range A1 to A3, then show the value in A3, otherwise type nothing. (ie is the value in cell A3 bigger than A1 and A2?)

An "if" formula is made up of:
=if(test,value if true,value if false) NOTE THE COMMAS.

In the above the test is whether the value in the cell is the biggest one so far.

If it is (ie if true), then show it to me.

If false, show me nothing.

The $ makes the cell absolute (ie the reference does not change). This means that when you copy the cell, it will always look at cell A1 as the top of your list.

The help file in Excel is also worth reading.

 

[Aladin Akyurek]

Why not invoke conditional formatting?

 

[R J ALDRIDGE]

Hi Aladin, I do not know what you mean?. Remember I am new to Excel formulas. Regards, Robert

 

[Aladin Akyurek]

Hi Aladin, I do not know what you mean?. Remember I am new to Excel formulas. Regards, Robert

Supposing that the balance figures are in A2:A100...

Select A2:A100.
Activate Format|Conditional Formatting.
Choose Formula Is for Condition 1.
Enter the following in the white box:

=$A2=MAX($A$2:$A$100)

Activate the Format button.
Choose an appropriate color from the Patterns tab.
Click OK, OK.

 

[R J ALDRIDGE]

Hi Aladin, AH I see what you mean now, thanks for that.
Regards, Robert