Got a credit card? use our Credit Card & Finance Calculators
Thanks to gpadsa,Steffers0,lansdown,Wasron,jfgw, for Donating to support the site
If statements with conditional formatting
If statements with conditional formatting
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.
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.
-
- Lemon Pip
- Posts: 50
- Joined: August 7th, 2019, 5:54 am
- Has thanked: 81 times
- Been thanked: 12 times
Re: If statements with conditional formatting
Maybe try asking on a dedicated Libre Office forum e.g. this one: https://superuser.com/questions/tagged/ ... dited=true
-
- Lemon Quarter
- Posts: 2098
- Joined: November 4th, 2016, 9:40 am
- Has thanked: 1041 times
- Been thanked: 849 times
Re: If statements with conditional formatting
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))
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))
-
- Lemon Quarter
- Posts: 2098
- Joined: November 4th, 2016, 9:40 am
- Has thanked: 1041 times
- Been thanked: 849 times
Re: If statements with conditional formatting
GrahamPlatt wrote:
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))
-
- Lemon Quarter
- Posts: 2006
- Joined: November 4th, 2016, 10:25 am
- Has thanked: 224 times
- Been thanked: 480 times
Re: If statements with conditional formatting
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)
Something like:
=OR(condition1>4,condition2>4)
This will return TRUE if either condition is satisfied, or FALSE if neither)
-
- Lemon Half
- Posts: 8438
- Joined: November 8th, 2016, 5:56 am
- Has thanked: 4497 times
- Been thanked: 3627 times
Re: If statements with conditional formatting
chas49 wrote: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
Re: If statements with conditional formatting
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.
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.
-
- Lemon Half
- Posts: 6050
- Joined: May 30th, 2021, 6:01 pm
- Has thanked: 1843 times
- Been thanked: 2067 times
Re: If statements with conditional formatting
raybarrow wrote: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
-
- Lemon Quarter
- Posts: 2708
- Joined: November 5th, 2016, 3:03 am
- Has thanked: 172 times
- Been thanked: 1801 times
Re: If statements with conditional formatting
raybarrow wrote: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.
Re: If statements with conditional formatting
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.
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.
-
- Lemon Quarter
- Posts: 2708
- Joined: November 5th, 2016, 3:03 am
- Has thanked: 172 times
- Been thanked: 1801 times
Re: If statements with conditional formatting
raybarrow wrote: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?
Re: If statements with conditional formatting
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.
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.
Return to “Technology - Computers, TV, Phones etc.”
Who is online
Users browsing this forum: No registered users and 12 guests