Calc (Openoffice / Libreoffice) If statements using 'between' or 'and'

It’s always annoyed me when I want to write an if statement where true is based upon more than one fact and I have to nest if statements. Today I looked at lots of other forumulas and found one that let me create an if_between effect. Like “IF a number is between 2000 and 3000 DO this ELSE that”

First, a little background on simple if statements

An if statement in calc is written:


test_something has to return true or false, so we can have if we have cell A1 with value of 6, A1=6 would return true whereas A1=5 would return false and A1=”six” would return false (the written six is not the same as the number 6).

If our test_something is true, calculate the do_if_thats_true section. If our test_something returns false, then calculate the do_if_thats_false section.

Example; if we put this formula in cell B1

=if(A1=6,"I see the number 6","Not 6")

then put ‘6’ inside A1, B1 is displayed as “I see the number 6”

if we put anything else inside A1 (‘six’,7,true,false,or even leave it empty) then B1 is displayed as “Not 6”

Using ‘between’ or ‘and’ in our if statements

That first example was easy. I often find myself wanting to do something more clever than that though. Here’s a real example I’ve just had to figure out for my work. We sell bedroom wardrobes and I’m creating a spreadsheet that given a particular width will work out which component parts we need to order.

I need to order a different number of lights depending on the width of the wardrobe. My lights come in strips of 1 metre or 2 metre lengths that can plug together and be cut to fit shorter lengths.  My wardrobes can be between 1.2m and 4m long.  Therefore:

  • if my wardrobe is 2m or less I need a single 2m length of light.
  • if my wardrobe is more than 2m but less than 3m I need a 2m and a 1m length of light
  • if my wardrobe is more than 3m i need 2 x 2m lengths of light.

I have two different product codes, let us call them ‘LIGHT1M’ and ‘LIGHT2M’ and I need my spreadsheet to tell the user how many of each code to order for any length of wardrobe the customer has.

So I create a spreadsheet that needs to one input from the user; the Wardrobe Length. I then have a row for each component. In column B I can calculate the quantity of the components needed from the wardrobe length. I’ve also copied my forumula to column C so you can see it (it is displayed because I haven’t put the = sign in front).

My results in column B are blue because I always colour calculated results blue so I and users know not to change the field manually.



In B3 (the easier part) I nest two if statements. If  “length is less than or equal to 3000”, then I need a single LIGHT2M so I can output the number 1. If my length is more than 3000 I’ll move into the ‘false’ part of the if statement. Here I’ve put another if statement (and you can nest if statements quite a long way – I think I’ve had seven in a row before – but it gets confusing so sometimes it makes sense to put each statement in a separate cell calculating based on the result of another cell) and that says if my “length is more than 3000” I want to return the number 2 as my quantity to order.  If I get here and my length is less than 3000 I return error. With my code here that can never happen. However I do this because I make mistakes (frequently!). If you’d like an example of an easy mistake to make, change B3 to read “if(B3<3000…” [remove the = from the <=] and put the wardrobe width as 3000 and see what happens. If I’m writing something that should never result in false (or true), I often output a text description to tell me where the error is.

Now the interesting bit

There is a forumla called ‘and’ which works like this:

=and(something_that_returns_true, something_else_that_returns_true, ...repeat other statements as you need...)

if every ‘something_that_returns_true’ in our and statement returns true, the result is true. I don’t think there is a limit to how many statements we can include in this but I’ve not pushed it very far. If one of them returns FALSE, our result is FALSE

In my case, I need a statement that will work as ifbetween(something), and I get the same effect by making two tests:

  • is my wardrobe greater than 2000?
  • is my wardrobe less than or equal to 3000?

Providing both of those are true, and(…) returns true to my if statement. So if both of those are true, I can return the value 1 as I need a 1m length of light if my wardrobe is between 2m and 3m long (and I realise I’m switching units, 1 metre = 1000 mm and I’ve been using mm in my spreadsheet  – sorry if that’s confusing!). If either of those tests were false, and(…) returns false and my if statement returns 0.

One last note:

If statements are using True and False.  the number 1 is also the value of True and the number 0 is also the value of False. There’s nothing to stop you confusing yourself by doing something like ‘if(something_is_true,0,1)’ then setting the cell display to ‘boolean’ [menu > formatting > cells > numbers] so when the result is true you actually see the result false!

Gitolite won't let server read repo

A note for my future self.

Summary: When adding a new production server to my cap deploy that will clone a repository from my gitolite server, First SSH into the gitolite server so the production server has the gitolite server in it’s trusted hosts file.

More detail:
A repository on my gitolite server wouldn’t allow a new server to read it. Cap Deploy failed with lines like:

failed: "sh -c 'git clone -q git@my.server.ip:myrepo.git ....

I couldn’t find any logs on the gitolite server to help. Whilst fault finding I read that gitolite replied to a standard SSH login with details about Repo’s you can access.

When I tried, I got the ssh untrusted host warning. Having added the host to my trusted hosts, my cap deploy worked fine.

Installing a new hard disk in a Mac = not fun

So I had to upgrade my mac hard disk. 12 months and I filled the 320Gb disk so I treated myself to a Seagate Momentus XT 750Gb disk.

It has NOT been fun. Problems so far include:

Initialise disk over the internet and restore from TimeMachine – fails to restore the recovery partition so cannot use FileVault2. Correct way: initalise disk over the internet and reinstall OS X Lion with a new temporary user that has a different user name (not one that’s within your TimeMachine backup). Then restore Mac from TimeMachine, then delete temporary user to keep things tidy.

BootCamp has been a nightmare. I eventually bought WinClone which has made things slightly easier. I got dual boot working, then setup FileVault2 which managed to prevent me logging in to the windows partition. I’m sure I had FileVault2 running before on the old disk with bootcamp and without any problems. Trouble is it takes hours for the encryption process, hours to clone the windows partition, hours to restore the windows partition. I have yet to get that working.

Now, I want to do some ruby coding and discover TimeMachine doesn’t save my ‘path’ information so Git didn’t work (it was backup up). TimeMachine also didn’t backup my /etc/hosts file (I had entries that weren’t important but finding them gone is a little disconcerting) and at the moment I can’t compile some software for a variety of errors.