High and low values in Excel charts

[the_observer]

Hi all,

i am trying to highlight high and low values in a chart as the title says.

I do not know excel but by searching around i found two ways to accomplish this but i have not managed one of them to work.

Both ways suggest to create two more columns (one for min and one for max values) and add the below formulas.
(Below i write only for high values ,min values are similar)

Way 1
(for high value) uses a formula like this :

=IF(B2=MAX(Value),B2,0)

(for low value) uses a formula like this :

=IF(B2=MIN(Value),B2,0)

Way 2
(for high value) uses a formula like this :

=IF($B2=MAX($B$2:$B$14),$B2,NA())

(for low value) uses a formula like this :

=IF($B2=MIN($B$2:$B$14),$B2,NA())

When i try either way i get a "the formula you typed contains an error" message

So after many tries i didn't manage any of the two ways to work.
Is it because they refer to another excel version than mine? I have 2007 while it is not clear in which excel version they refer.
Probably i am doing something wrong.

I attach the excel in case some good man could help me out.

Thank you for your time reading this and excuse my lame English.

Regards,
the_observer.

 

[NARAYANK991]

Hi ,

There is nothing wrong with your formulae ; probably your Excel version uses the semi-colon ; as a separator instead of the comma.

Narayan

 

[the_observer]

Thank you much Narayank.

The second way works like a charm.
Thank you mate.

The first way though returns a #NAME? in the cell.

I will continue with the 2nd way i guess.

Thank you again.

Regards,
the_observer.

 

[NARAYANK991]

Hi ,

The error is because Value is actually to be created ; it is a named range.

Go to the Name Manager , and create a named range called Value , and in the Refers To box , enter =$B$2:$B$14

Now , the formula will work.

Narayan

 

[the_observer]

Knowledge is power .

Narayank powerful !

Thank you ,

Regards,
the_observer.