Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    selecting correct range (2003)

    I am using vlookup tables to get data based on the referenced date's day of the year and whether it is a leap year or not. There are three date ranges for the lookup tables, and for the last date range, one data set is used 1 year in 3, and the other data set is used the remaining 2 years.

    I have 8 named ranges.:
    1. 2/1/76 to 2/8/76, non-leap year
    2. 2/1/76 to 2/8/76, leap year
    3. 2/9/76 to 11/30/81, non-leap year
    4. 2/9/76 to 11/30/81, leap year
    5. 12/1/81 to present, non-leap year, 1 year in 3
    6. 12/1/81 to present, leap year, 1 year in 3
    7. 12/1/81 to present, non-leap year, 2 years in 3
    8. 12/1/81 to present, leap year, 2 years in 3


    I have gotten it to work, except for the 1 year out of 3. The 1 year in 3 could start in 1981, 1982, or 1983 and having the option to pick the start year would be nice. I tried using a MOD(date,3)=0 command to pick out the 1 year in 3, but that gave me too many arguments. My basic logic, excluding the 1 year in 3, is:

    IF column A date < 2/1/76
    THEN determine if it is a leap year or not and select correct leap year/non-leap year vlookup table and value for that date
    ELSE IF column A date > 11/30/81
    THEN determine if it is a leap year or not and select correct leap year/non-leap year vlookup table and value for that date
    ELSE column A date not < 2/1/76 or > 11/30/82, determine if it is a leap year or not and select correct leap year/non-leap year vlookup table and value for that date.

    The actual formula using the 2 year in 3 data set is:
    =IF(A19<DATE(1976,2,9),IF(MOD(YEAR(A19),4)=0,VLOOK UP(A19-DATE(YEAR(A19),1,0),S59_366_72_to_76,2,FALSE),VLOO KUP(A19-DATE(YEAR(A19),1,0),S59_365_72_to_76,2,FALSE)),IF( A19>DATE(1981,11,30),IF(MOD(YEAR(A19),4)=0,VLOOKUP (A19-DATE(YEAR(A19),1,0),S59_366_81_to_current_2yr,2,FA LSE),VLOOKUP(A19-DATE(YEAR(A19),1,0),S59_365_81_to_current_2yr,2,FA LSE)),IF(MOD(YEAR(A19),4)=0,VLOOKUP(A19-DATE(YEAR(A19),1,0),S59_366_76_to_81,2,FALSE),VLOO KUP(A19-DATE(YEAR(A19),1,0),S59_365_76_to_81,2,FALSE))))

    Any suggestions as how to do this? Any ideas to improve the kludge I have now would also be appreciated.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selecting correct range (2003)

    I don't understand what you're trying to do. What in heaven's name does "2/1/76 to 2/8/76, non-leap year" mean? 1976 *was* a leap year?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: selecting correct range (2003)

    I don't understand what difference it makes if it is a leap year or not. How is your lookup table structured?

    If you had a table with the first column dates in some arbitrary "start year" (should be a leap year) and had each year of data in columns next to it, you could vlookup for the "row" based on the month and day in that year and move over to the column with the correct year.

    Steve

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: selecting correct range (2003)

    guys:
    thanks for your help, but I decided to hardwire the data. I was spending more time than I had trying to make a slick formula instead of getting a bigger hammer and beating my way through the data.

    Again, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •