# NEW HIGH INDICATOR FORMULA

#### R J ALDRIDGE

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

##### MrExcel MVP
hi - welcome to the board!

this the sort of thing you mean?

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

##### New Member
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

##### MrExcel MVP

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

#### R J ALDRIDGE

##### New Member

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

##### Well-known Member
=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.

##### MrExcel MVP

Why not invoke conditional formatting?

#### R J ALDRIDGE

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

##### MrExcel MVP
R J ALDRIDGE said:
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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,025
Messages
5,856,898
Members
431,837
Latest member
megantang

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back