[Solved] Conditional Formatting Difficulties

[RBrehm]

I have a spreadsheet that I use to calculate scores for a competition. The point values for each team are totaled in column "AD", and I currently use three conditional format rules to highlight the top three scores (Gold, Silver and Bronze). I would like to try to change the conditional format formula to make one of the questions (Column AA) required (require that cell to contain a value >0) or the score will not be highlighted, even if it's in the top 3.

I'll be glad to upload the file when I get home if needed.

Thank you for your help!

[Zizi64]

I not understood it exactly: Do you want to use more than three conditions, or you want combine the conditions with some boolesan functions AND()/OR()?

- The LibreOffice can handle more than three conditions.
- The Boolean functions are available for the conditional format feature in both of opensource office suites.

[keme]

Another way to have automated formatting with more than 3 alternative formats is to use the STYLE() function. Note that this function will also work when used in a conditional formatting formula (selecting "Formula is..."), so you can use it with data input cells.

[Lupp]

Note that this function {STYLE} will also work when used in a conditional formatting formula (selecting "Formula is...")

@keme: There are issues. I would judge the trick (slightly?) toxic. Surely nothing for a beginner.

1. The working is not documented anywhere, afaik. It should be considered subject to changes without notice.
2. The CF formula has to ensure a result of 0 (zero). Otherwise one cell style may be assigned fix first on calculation but then be overlayed by the conditional style defined
for TRUE (<>0) result. This without notifying the cell object as is the way CF works. (Ok. The funny effects may be pets to sorcerers.)
3. It is against the grain for CF. CF is view oriented.
3.b The conditions of CF are only evaluated for the current view of the active sheet. A rare but really funny and unsolvable (next to) misbehaviour will occur if some results in the document depend on style-aware functions like ISLOGICAL.
3.c Same with side-effects of user functions. Similar with user functions looking for cell styles.
4. Effects as mentioned under 3. will not trigger recalculations if 'AutoCalculate' is on.
5. During my experiments (under LibO) I often got an irritating flicker when editing cells subject to such a fake CF.

[RBrehm]

Sorry I didn't provide more information (tends to happen when I'm working from my phone).

I use conditional formatting on column AD with the equations =LARGE($AD$1:$AD$22;n) (n is replaced with 1, 2, and 3 for each color (gold, silver, bronze)). This works and does highlight the highest, second and third in the correct respective color. But any time I try to also require cell AA in the respective row to have data (Such as with AND(LARGE($AD$1:$AD$22;1);$AA1<>""), it will highlight each cell in AD that contains data as gold, no matter if it's the highest or not.

I'll be home this evening and able to upload the when I can get to a computer. Thanks!

[Lupp]

In advance of introducing the AND part you worked in the mode 'Value is'? This compared the mumeric result of

Code: Select all

LARGE($AD$1:$AD$22;1)

with the content of the respective cell ($AD1 assumed), and the condition returned TRUE if this was actually the score for the first rank.
Introducing the AND part you had to switch to the mode 'Formula is' and as a part of that formula

Code: Select all

LARGE($AD$1:$AD$22;1)

will surely return a numeric value different from 0 (zero). As any value <>0 is taken fror TRUE when evaluated by logical expressions the AND function always gets passed TRUE in the first parameter position independent of the value calculated for the current cell. If the second parameter also passes TRUE the final result will be TRUE again and the corresponding style will be overlayed.

Code: Select all

AND(LARGE($AD$1:$AD$22;1)=$AD1;$AA1<>"")

should do as you expect - if being empty is the only way for $AA1 to miss the condition you put as "value > 0" first.

(Editing:)
Are you aware of the fact that CF will have to evaluate a LARGE expression 3 times for each cell NOT containing a medal-rank value in $AD$1:$AD$22? And if you want to apply that CF to complete rows (or big parts of them) this will multiply respectively.
OK. To evaluate ranks for 22 cells or even for 22 x 15 cells (if 15 columns are visible, e.g.) isn't a big chunk of labor for hard- and software nowadays, but basically it is inefficient.
Anyway I would surely use a dedicated column for the RANK. CF then has direct access to the results it needs, and it is very simple to MATCH / INDEX / LOOKUP data associated with the winners for printouts or whatever.

[RBrehm]

Code: Select all

AND(LARGE($AD$1:$AD$22;1)=$AD1;$AA1<>"")

Thanks for the reply. Fortunately I'm only evaluating the one column, and it should never have more than 20 values.

This got me closer, but not quite there yet. I've attached the sheet in question with your recommended changes and put in some dummy data.

In the given example, there are 4 teams with their final scores shown in column AD (where the conditional formatting is applied). The required question that I mentioned is labeled "Wager" (Column AA).

Since the "Brown" team (Row 6) did not complete the final question (Labeled "Wager", Column AA), I do not want their score highlighted even though it is the highest, which your equation accomplished.

The remaining 3 teams should be highlighted in order of their score (Gold, Silver, Bronze), but the formula only highlights the highest score of those applicable in silver, the second highest in bronze, and the third not at all. I'm assuming that the sheet sees that the "Red" team (row 3) has the second highest score and has completed the required question, which is why it is silver, but I want to have it formatted with gold (Seeing as it's the highest score of the teams that completed the required question.) and so on...

Any suggestions?

Edit: The sheet say it's protected, but it doesn't have a password.

[Persian_Assassin]

It should be mentioned that the black selector box influences the conditional formatting. In what logical manner or reasoning, I have no clue. I've come to the conclusion that this software is garbage and makes even the simplest tasks a headache. Why is it not as simple as dragging a box over my range?

[jeffs12]

Working from the suggestion from Lupp, I've added two columns and changed the CF for column AD. I believe that this is accomplishing what you are asking for.

[RoryOF]

It should be mentioned that the black selector box influences the conditional formatting. In what logical manner or reasoning, I have no clue. I've come to the conclusion that this software is garbage and makes even the simplest tasks a headache. Why is it not as simple as dragging a box over my range?

If you don't like it, don''t use it.

[RBrehm]

Working from the suggestion from Lupp, I've added two columns and changed the CF for column AD. I believe that this is accomplishing what you are asking for.

Thank you! Sometimes I just tend to try to over complicate things, this is elegant and functional

[Lupp]

..., this is elegant and functional.

True.
This following part of the original question

...to make one of the questions (Column AA) required (require that cell to contain a value >0) or the score will not be highlighted, even if it's in the top 3.

was gravely misleading (and time-wasting), however. What you actually wanted was to exlude rows not having a (positive?) value in column AA completely from the evaluation.

(Doing this directly by formulas entered into the CF conditions would complicate the job again substantially as array evaluation would be needed.)