[Solved] else if, case or alternatives?

Discuss the spreadsheet application

[Solved] else if, case or alternatives?

Postby mpcengineering » Tue Jun 24, 2008 7:40 pm

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?

Postby mpcengineering » Tue Jun 24, 2008 8:27 pm

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   Expand viewCollapse view
=$'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
mpcengineering
 
Posts: 15
Joined: Tue Jun 24, 2008 7:19 pm

Re: else if, case or alternatives?

Postby Villeroy » Tue Jun 24, 2008 8:28 pm

VLOOKUP and MATCH are your friends for up to 65536 styles ;)
download/file.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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: else if, case or alternatives?

Postby keme » Tue Jun 24, 2008 10:48 pm

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 133 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 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3391
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: else if, case or alternatives?

Postby mpcengineering » Wed Jun 25, 2008 9:51 am

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
mpcengineering
 
Posts: 15
Joined: Tue Jun 24, 2008 7:19 pm

Re: else if, case or alternatives?

Postby Villeroy » Wed Jun 25, 2008 11:02 am

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28825
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: else if, case or alternatives?

Postby mpcengineering » Wed Jun 25, 2008 12:19 pm

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?

Postby mpcengineering » Wed Jun 25, 2008 2:34 pm

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
mpcengineering
 
Posts: 15
Joined: Tue Jun 24, 2008 7:19 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests