Apache OpenOffice (AOO) Bugzilla – Issue 79328
Incorrect Calculation with vlookup
Last modified: 2007-08-10 10:45:23 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.
Created attachment 46583 [details] Spreadsheet to evaluate earliest and latest dates for Easter
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
added "needmoreinfo"
Hi, seems to be a double to Issue 76623 . Frank *** This issue has been marked as a duplicate of 76623 ***
closed double