If statements with conditional formatting

[raybarrow]

Hi Folks,
Mrs B, AKA Brown Owl, has a spreadsheet for Brownies which we have been modifying to highlight indiviual cells depending on the reults in other cells.
Each girl can attain bages by completing task within a certain theme. As a task is complete a 'Y' is entered against it. The 'Y's are totalled and if the target is reached, say 5, then that cell colour changes to highlight it as completed. All that is fine.

We can highlight a girl's name depending on that that target being reached with conditional formatting. Again that is fine.

Most badges are a single theme which is fine, but some badges have a choice of two themes but they cannot mix and match so either complete theme 1 or theme 2. Again we can use two separate sets of conditional formatting to highlight the girl's name on completion of theme 1 or theme 2.
So condition 1 says 'If target for theme one is greater than 4 then colour name green'.
Condition 2 says 'If target for theme two is greater than 4 then colour name green'.
This works but being me I would like one statement that says 'If target for theme one is greater than 4 or target for theme two is greater then 4 then colour name green'

I'm struggling with the 'or' part and subsequent colouring of the name cell. Mrs B uses Excel, I use LibreOffice but either will be fine if I just get my head around the last bit. The current two separate conditions set up works fine, so it's not urgent, but I'd like to get my brain in gear.

Any help would be appreciated,
Ray.

[sloth]

Maybe try asking on a dedicated Libre Office forum e.g. this one: https://superuser.com/questions/tagged/ ... dited=true

[GrahamPlatt]

Have a look at the SUMIFS function.

Discussed in this thread:
viewtopic.php?f=9&t=33115&p=476757#p476631

Edit: you could nest the IFs … IF(condition 1 > 4, couloir green, IF(condition 2 > 4, colour green, colour red))

[GrahamPlatt]

Edit: you could nest the IFs … IF(condition 1 > 4, couloir green, IF(condition 2 > 4, colour green, colour red))

But meant

IF(condition 1 > 4, colour green, IF(condition 2 > 4, colour green, colour red))

[Stompa]

Take a look here:

https://www.got-it.ai/solutions/excel-c ... matting-or

[chas49]

Another approach is to use a "helper" column to test for the badge award, and then use conditional formatting to look at the result in the helper column.

Something like:

=OR(condition1>4,condition2>4)

This will return TRUE if either condition is satisfied, or FALSE if neither)

[servodude]

Another approach is to use a "helper" column to test for the badge award, and then use conditional formatting to look at the result in the helper column.

Something like:

=OR(condition1>4,condition2>4)

This will return TRUE if either condition is satisfied, or FALSE if neither)

I would second this approach as the easiest to get right and maintain
I'd probably have enough helper columns that the info could be seen at a glance so that you can easily confim it is correct - and then hide the ones you don't need to see
That's beacuse I find spread sheet function syntax to be easier to read when there are as few fields as possible

For example, in this case that would probably be columns for: Theme1_completed, Theme2_completed, and ShouldBeGreen (which would be the field used for conditional formatting of the name)

hope that makes sense
-sd

[raybarrow]

Hi Folks,

Thanks for all the replies. I've been thinking along the same lines of extra/hidden columns. The spreadsheet is a 'supplied' one with access to a lot of it, but perhaps not a good idea to alter the basic structure too much. Seems a step to far to do it all I want in one formula.

'Perfection is the enemy of the good'. In which case we will probably stick with what we have as it works.

On the plus side I've learnt more about IFS, OR, ISFORMULA(never heard of that one).

Cheers,
Ray.

[pje16]

On the plus side I've learnt more about IFS, OR, ISFORMULA(never heard of that one).

Cheers,
Ray.

ISFORMULA(never heard of that one).
me neither, but it looks a handy one

[Hallucigenia]

I would like one statement that says 'If target for theme one is greater than 4 or target for theme two is greater then 4 then colour name green'

I'm struggling with the 'or' part and subsequent colouring of the name cell. Mrs B uses Excel, I use LibreOffice but either will be fine if I just get my head around the last bit. The current two separate conditions set up works fine, so it's not urgent, but I'd like to get my brain in gear.

Use conditional formatting with a formula, no hidden columns needed. In Excel 2013 :

Say you want to test the values in A1 and B1 and apply a colour to C1
Select C1
Go to Home tab
Select the Conditional Formatting dropdown on the ribbon bar
Choose New Rule
Select "Use a formula to determine which cells to format"
Enter :

=OR($A$1>4,$B$1>4)

into the box and select a format to go green
OK, OK

Being Microsoft this kind of thing is powerful but the power is rather hidden, whereas eg Google Sheets makes the application of "stacked" conditional formats rather more obvious.

[raybarrow]

Hi Hallucigena,

Wonderful! The 'OR' statement works in Excel without the need for 'hidden helper' columns. Mrs B's needs are now met. I must have not enetered it correctly when I tried it, too many formulae, too few brain cells no doubt.

Whilst the formula works in LibreOffice if only gives a TRUE/FALSE result, not suused the colour formatting yet. I'll try Sloth's suggestion for the LibreOffice superuser forum now I can make it work in Excel.

Never too old to learn, it just gets harder,
Ray.

[Hallucigenia]

Mrs B's needs are now met.

This is always important.

I don't know Libre well enough to comment specifically, but what happens if you take a spreadsheet that works in Excel and import it, if it works then it will show you how Libre does it?

[raybarrow]

Hi Folks,

Made it! in LibreOffice under Format>Conditional>Color Scale is the option to use a formula/value/date to change the cell colour. The 'OR' statement works fine in there. Colours are picked of a style list with the option to create a custom style. I have to admit Excel's version is more intuitive but LibreOffice can do it. It's the old problem of flitting between two systems. Don't even suggest getting Mrs B to learn LibreOffice!

...and relax,
Thanks to one and all,
Ray.