NEW HIGH INDICATOR FORMULA

R J ALDRIDGE

New Member
Joined
Mar 4, 2004
Messages
5
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
Joined
Mar 4, 2004
Messages
5
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
Joined
May 15, 2003
Messages
8,638
How about changing Paddys formula to:

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

R J ALDRIDGE

New Member
Joined
Mar 4, 2004
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Why not invoke conditional formatting?
 

R J ALDRIDGE

New Member
Joined
Mar 4, 2004
Messages
5
Hi Aladin, I do not know what you mean?. Remember I am new to Excel formulas. Regards, Robert
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top