PDA

View Full Version : Solved: Max or Large Function



zoom38
05-16-2006, 02:29 PM
I have a conditional format where it test for the maximum value and then highlights it. The problem arises when the values it is testing are carried to decimal places. For instance, the formula Max(M$29:M$46) highlights both of the following values as the highest, 1.722 and 1.000. The rest of the values tested are below 1.0. How do I modify this formula to only highlight the highest value in this instance? I tried using the Large function with the same result.

Thanks
Gary

Zack Barresse
05-16-2006, 02:35 PM
Can you post a sample spreadsheet with this CF on it?

zoom38
05-16-2006, 02:45 PM
The problem is in the slugging pct column, M29:M46.

zoom38
05-16-2006, 02:52 PM
When I shortened the file it changed the values in m29:m46. If you go to game 1 and 2 and give two players 4 hr's that will put the slug pct over 1.000 which will re-create the problem.

Zack Barresse
05-16-2006, 03:05 PM
You need to use absolute referencing when you use conditional formatting and comparing to a static range. Select M29 only and look at your conditional format. Then select M46 only and look at it's conditional format. Different, and it's because of the reference style used. Add thos "$" signs and you'll be fine. And you shouldn't even need the first CF I wouldn't think.

zoom38
05-16-2006, 03:32 PM
Thanks FF, you are right, I don't know how that one got by me. Most of the other CF's had the absolute references in it. The 0 condition is just so if they are all 0 I don't want them all highlighted.

Gary

Zack Barresse
05-17-2006, 10:37 AM
But wouldn't that work out in the FALSE clause of your CF? Just seems to be a waste of a [limited] CF is all.

Take care Gary. :)

zoom38
05-18-2006, 11:13 AM
I'm not sure what you mean by the false clause FF. I have a CF as follows:

Condition 1
Cell Value Is Equal To =Max(C$5:C$22)

and the format is cell fill color yellow.

Now with this if the range C5:C22 is all 0, then they all will color yellow. This is the reason to change the above to the following.

Condition 1
Cell Value Is Equal To 0 no format set

Condition 2
Cell Value Is Equal To =Max(C$5:C$22) format cell fill color yellow

So when the range C5:C22 is all 0, there is no change in cell fill color.
When there is a value in the range other than 0 there will be a change in cell fill color for the max value in the range.

If i'm doing something wrong or if there is an easier way please let me know.

Thanks
Gary

Zack Barresse
05-18-2006, 11:47 AM
Ah, I see, so there is a possiblity of all being zero. Well, you could setup a check in just a single CF ...


=(SUM(C$5:C$22)>0)*(C5=MAX(C$5:C$22))

Be sure to have C5 as the activecell in the selection when using this though.