Ask Your Question
0

Error trying to lookup date range

asked 2015-05-23 13:13:32 +0200

sonogenics gravatar image

updated 2015-08-26 21:18:07 +0200

Alex Kemp gravatar image

I have imported a spreadsheet create in Microsoft Excel 2010 into Calc and most things work fine apart from 1 fairly critical thing. I have 1 sheets with a selection of date ranges identified, and another sheet with a single date and I am trying to determine what period this single date lies in. This works perfectly in excel and I am able to get the expected output, something like:

Start Date | Week
2015-02-01 | 6
2015-02-14 | 8

The formula in the Week cell is:
=LOOKUP(2,1/((B2>=$'5 - Week Periods'.$B$2:$B$54)*(B2<=$'5 - Week Periods'.$C$2:$C$54)),$'5 - Week Periods'.$A$2:$A$54)

And the 5 - Week Periods sheet looks something like this:

Week | Start | End
1 | 12/27/2014 | 1/2/2015
2 | 1/3/2015 | 1/9/2015
3 | 1/10/2015 | 1/16/2015
...

However, when I try to run this in Libreoffice Calc (4.2.7.2), the calculcated week numbers work fine until I change the lookup date and then I get #DIV/0!

It would appear that (B2>=$'5 - Week Periods'.$B$2:$B$54)*(B2<=$'5 - Week Periods'.$C$2:$C$54) is returning 0. Any ideas on how I can fix this or is there an alternate way I can get this to work?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2015-05-24 04:45:00 +0200

Vitaly gravatar image

updated 2015-05-24 04:45:44 +0200

It's a little bit difficult to quickly reproduce your data structure to find out why the formula doesn't work. But if the weeks at "5 - Week Periods" sheet are listed one-by-one without skipping days then, IMHO, your way of calculation is too complicated.

Consider this:
=WEEKS(A1;First;0)+1
Here, A1 is your "StartDate" cell, and "First" is name of cell that contains date of beginning of the first week (12/27/2014 in your sample). The third parameter may be set to 0 or 1 depending on what exactly you want to calculate - see help file for details regarding WEEKS function.

edit flag offensive delete link more
0

answered 2015-05-24 07:42:12 +0200

karolus gravatar image

Hallo

@Vitaly +1 for your Formula,

using the '5 - Week Periods'.$B$2:$B$54 reference:

=MATCH( B2; $'5 - Week Periods'.$B$2:$B$54)

also does the job

edit flag offensive delete link more
-1

answered 2015-05-24 12:27:44 +0200

m.a.riosv gravatar image

A question with a similar lookup issue in: How do I lookup and return a most recent value?

edit flag offensive delete link more

Comments

and -1 for your link --- this is not a lets made complicate-contest

karolus gravatar imagekarolus ( 2015-05-24 12:42:36 +0200 )edit

Please @karolus forgive me the life, my intellect is far from yours. With your permission I was thinking about show, how to solve this particular case of lookup in calc. But if a master says it's not fine, sure it's not fine.

m.a.riosv gravatar imagem.a.riosv ( 2015-05-25 01:11:05 +0200 )edit

Don't bullshit -- I'm pretty sure youre able to follow the two simple solutions here - no needs to provide an overhelmed one!

karolus gravatar imagekarolus ( 2015-05-25 08:43:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-05-23 13:13:32 +0200

Seen: 180 times

Last updated: May 24 '15