[Solved] else if, case or alternatives?

Discuss the spreadsheet application
Post Reply
mpcengineering
Posts: 15
Joined: Tue Jun 24, 2008 7:19 pm

[Solved] else if, case or alternatives?

Post by mpcengineering »

Hi all,

I'm trying to apply a custom style to a cell according to the numerical value of that cell, but there are 7 different styles I want to apply according to the value of that cell. So essentially I have to test for 7 different values and apply a style accordingly. Testing for one value and applying a style isn't a problem:

=$'Bay 405'.$F$48+STYLE(IF(CURRENT()>300;"Red2";"Red0"))

This just totals the value of cells from another worksheet names 'Bay 405' and then applies the style 'Red2' if it totals over 300, otherwise 'red0' is applied. Works fine, but I can't seem to work out how to test for 7 values, I can't work out how to string together multiple if statements, and other statements such as 'else if' and 'case' don't appear to exist as far as I can tell. Is this possible?

Thanks

Jon
Last edited by mpcengineering on Wed Jun 25, 2008 2:35 pm, edited 1 time in total.
mpcengineering
Posts: 15
Joined: Tue Jun 24, 2008 7:19 pm

Re: else if, case or alternatives?

Post by mpcengineering »

This always happens, as soon as I post, I suddenly think of a way to do it :lol:

It works, but its not pretty, just a TON of nested if statements, surely there is a better way?

Code: Select all

=$'Bay 405'.$F$48+STYLE(IF(CURRENT()>525;"Red7";IF(CURRENT()>450;"Red6";IF(CURRENT()>375;"Red5";IF(CURRENT()>300;"Red4";IF(CURRENT()>225;"Red3";IF(CURRENT()>150;"Red2";IF(CURRENT()>75;"Red2";"Red0"))))))))
UGLY
OOo 2.4.X on Debian
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: else if, case or alternatives?

Post by Villeroy »

VLOOKUP and MATCH are your friends for up to 65536 styles ;)
http://user.services.openoffice.org/en/ ... php?id=667
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: else if, case or alternatives?

Post by keme »

Note the third parameter to the MATCH() function. Zero indicates that the lookup range is unsorted and will only succeed on exact match, while +/- 1 indicate a sorted lookup range and will provide the >/< functionality. The help for the MATCH() function explains it in detail.
Besides the lookup functions, you can use INDEX() or CHOOSE() to pick the name of the style you want to use. They work in different ways, and the choice is more or less a matter of taste.

The attached file illustrates the difference between "sorted" and "unsorted" MATCH(), and the use of CHOOSE(). Enter different values in B1, and watch what C1 and D1 returns.
test.ods
(7.33 KiB) Downloaded 239 times
(I didn't include the STYLE() element, though, so it's not as elegant as Villeroy's all-dancing colours. You'll have to insert that yourself.)

FWIW, I believe you reached the limit of IF()'s capabilities. (I believe I read somewhere that IF() can only be nested 7 levels deep. Too lazy to test it, though.) In theory you could build a "selection tree" to have up to 128 different values, but that would be an increasingly unmanageable one-liner.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
mpcengineering
Posts: 15
Joined: Tue Jun 24, 2008 7:19 pm

Re: else if, case or alternatives?

Post by mpcengineering »

villeroy, keme, tyvm for the examples! I'll have a look through your suggestions and see if I can't get it working. Also useful to note you can define the cell colour without having to save it as a style, instead having the RGB values defined in a cell, very useful!

I'll change the post title to include solved once I have it working.
OOo 2.4.X on Debian
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: else if, case or alternatives?

Post by Villeroy »

Also useful to note you can define the cell colour without having to save it as a style, instead having the RGB values defined in a cell, very useful!
If you refer to my document where something like "RGB_00FF00" is looked up, then you will notice that "RGB_00FF00" is the name of a style actually.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mpcengineering
Posts: 15
Joined: Tue Jun 24, 2008 7:19 pm

Re: else if, case or alternatives?

Post by mpcengineering »

Ah yes, I see that now. My bad.
OOo 2.4.X on Debian
mpcengineering
Posts: 15
Joined: Tue Jun 24, 2008 7:19 pm

Re: else if, case or alternatives?

Post by mpcengineering »

Ok, got this working now:

=$'Bay 405'.$F$48+IF(CURRENT()>0;STYLE(VLOOKUP(CURRENT();$G$4:$H$11;2;1));STYLE("Red0"))

So based on the example Villeroy attached, this colours the cells correctly according to their numerical value. If the value is 0, no colour is applied, and I used the 'otherwise' value of the if statement to apply a white colour in any other case. This is is to cater for if the value of a cell is changed to > 0 and then back to = 0 where without it, the cell would retain the colouring from when it was > 0.

Thanks again for your help, I will change the title of the initial post to include [SOLVED]

Jon
OOo 2.4.X on Debian
Post Reply