Issue 79328 - Incorrect Calculation with vlookup
Summary: Incorrect Calculation with vlookup
Status: CLOSED DUPLICATE of issue 76623
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.2.1
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords: needmoreinfo
Depends on:
Blocks:
 
Reported: 2007-07-06 14:53 UTC by slush
Modified: 2007-08-10 10:45 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Spreadsheet to evaluate earliest and latest dates for Easter (18.28 KB, text/plain)
2007-07-06 14:55 UTC, slush
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description slush 2007-07-06 14:53:13 UTC
A spreadsheet has been designed as follows

All cells are protected except cell A2.

Cell A1 to E1, column headings Year, Month, Day, MonthDay, Date.

A four digit number is entered in cell A2, eg 2007.

Cell A3 containes the formula =A2+1. This is copied down to cell A102 to give a 
sequnce of numbers (years).

Cell E2 contains the formula =EASTERSUNDAY(A2). This is copied down to cell 
E102 to give the Easter Sunday date for each year in column A.

Cell B2 contains the formula =TEXT(E2;"mmmm"). This is copied down to cell B102 
to give the month in which Easter occurs every year.

Cell C2 contains the formula =VALUE(TEXT(E2;"dd")). This is copied down to cell 
C102. This gives the day of the month in which Easter occurs.

Cell D2 contains the formula =IF(B2="March";300+C2;400+C2). This is copied down 
to cell D102. This formula will give a result like 402 for 2nd April or 327 for 
27th March.

In cell G3 the formula =MIN(D2:D102) is entered.
In cell G4 the formula =G3+1 is entered.
In cell G5 the formula =G4+1 is entered.
In cell G6 the formula =G7-1 is entered.
In cell G7 the formula =G8-1 is entered.
In cell G8 the formula =MAX(D2:D102) is entered.

In cell H3 the formula =VLOOKUP(G3;$D$2:$E$102;2;0) is entered. This is copied 
down to cell H8.

The dates in column E and column H are displayed in the format 
Sunday 12 April 2009

If 2009 is entered in cell A2 the results in the results in cells G3 to H8 are 
as follows.

325	Sunday 25 March 2035
326	Sunday 26 March 2062
327	Sunday 27 March 2016
423	Sunday 23 April 2079
424	Sunday 24 April 2011
425	Sunday 25 April 2038

Everything correct. 25th March is the earliest date on which easter occurs 
between 2009 and 2109 and it occurs in the year 2035. 25th April is the latest 
that Easter occurs.

If one then enters 1965 in cell A2 the following results are obtained

323	Sunday 23 March 2008
324	#N/A
325	Sunday 25 March 2035
423	Sunday 23 April 2000
424	Saturday 23 April 2011
425	Saturday 24 April 2038

If one saves the spreadsheet Sunday 23 April 2000 becomes Saturday 22 April 
2000. I do not have a problem with there being no Easter Sunday on the 24th 
March, however all the dates that are returned by VLOOKUP should be Sundays, 
Easter Sundays. I have also noticed that if one selects the cells H6 to H8 the 
formulas are evaluated corectly.
Comment 1 slush 2007-07-06 14:55:25 UTC
Created attachment 46583 [details]
Spreadsheet to evaluate earliest and latest dates for Easter
Comment 2 frank 2007-07-06 21:35:18 UTC
Hi,

could not reproduce the problem. What is the locale used ? English US ?
Is it possible that an autosave process took place whilst you tried to calculate
this thing ?

Thanks for your help.

Frank
Comment 3 gercokees 2007-07-11 13:35:38 UTC
added "needmoreinfo"
Comment 4 frank 2007-08-10 10:45:01 UTC
Hi,

seems to be a double to Issue 76623 .

Frank

*** This issue has been marked as a duplicate of 76623 ***
Comment 5 frank 2007-08-10 10:45:23 UTC
closed double