Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Help with a complex formula or lookup table

  1. #1

    Help with a complex formula or lookup table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am way over my head on this, but it seems to me my problem could be solved w/ either a formula or look-up of some sort. In either case, it's beyond my skill set. Easier to visualize than explain so I have attached the sheet. Thanks for any thoughts on the problem.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I don't understand how you got the results for Group 2? Can you explain?

    Also, if you have crossposted to other forums, please indicate them here.


  3. #3
    Unless I screwed something up in my sample, a person in Group two has to register twice a year; once during the birth month and the other 6 months later. Assuming it's January, for a person born in Jan the next date would be Jul and if born in Feb, the next registration date would be Aug. (and yes, I see that I screwed up the group 2 people) See that's why I need to automate it. I'll revise the sample. Thanks.

    Oh, and I have a similar post on a Google sheets forum, but it's going to be a different solution and I'm not getting very far w/ it. Thanks.
    Last edited by wmpwi; 2014-01-30 at 02:58 PM.

  4. #4
    Another way to look at it is Annual, Semi Annual, and Quarterly with the start of each registration period equaling the birth Month. Born in Jan as a Group 3 means traditional quarterly months, but born in Feb means an offset by 1 month so they're going to be Feb, May, Aug, Nov.

    Group 2 people born in Jan = Jan & Jul, born in Feb = Feb & Aug, Mar = Mar & Sep and so on.
    and Group 1's are annual so that one is easy.


    The whole thing would be easy if it were 15 - 20 people, but is over 100 and they come an go so anything that automates it (as you can see from my earlier error) would help.

    **** and I see I screwed up group 3. I must have moved something around and messed up all the expected results in col D

    If I'm to incompetent to ask the question, I would certainly understand disqualifying my request. (so embarrassed)
    Last edited by wmpwi; 2014-01-30 at 03:14 PM. Reason: I screwed up the sample

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by wmpwi View Post
    Another way to look at it is Annual, Semi Annual, and Quarterly with the start of each registration period equaling the birth Month. Born in Jan as a Group 3 means traditional quarterly months, but born in Feb means an offset by 1 month so they're going to be Feb, May, Aug, Nov.

    Group 2 people born in Jan = Jan & Jul, born in Feb = Feb & Aug, Mar = Mar & Sep and so on.
    and Group 1's are annual so that one is easy.


    The whole thing would be easy if it were 15 - 20 people, but is over 100 and they come an go so anything that automates it (as you can see from my earlier error) would help.

    **** and I see I screwed up group 3. I must have moved something around and messed up all the expected results in col D

    If I'm to incompetent to ask the question, I would certainly understand disqualifying my request. (so embarrassed)
    This is what I was thinking it was.. that is why I was confused about Group 2 results you showed. Maybe this can be done without tables. Is it only those 3 groups?

    Also, if you can repost the expected results to make sure the solution gives the same results.. .that would be good.


  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    As an initial go at it, this is my thinking about using just a formula... try this formula and let me know where it is going wrong, if at all..

    =IF(B6=1,TEXT(A6,"mmm"),IF(B6=2,TEXT(EDATE(A6,6),"mmm"),TEXT(EDATE(A6,3),"mmm")))

    which can be shortened to:

    =TEXT(EDATE(A6,CHOOSE(B6,0,6,3)),"mmm")


  7. #7
    I think we're getting somewhere, but my problem is this is a dynamic sheet that references A2 (today's date) such that when the date advances, the next registration month changes. Today it's Jan so a Jan student in Group 3 should show Jan in Col D, but in a few days it's Feb so the Jan students in Group 3 should show March in Col D. Hope that's starting to clear up my problem. Thanks.

    Quote Originally Posted by NBVC View Post
    As an initial go at it, this is my thinking about using just a formula... try this formula and let me know where it is going wrong, if at all..

    =IF(B6=1,TEXT(A6,"mmm"),IF(B6=2,TEXT(EDATE(A6,6),"mmm"),TEXT(EDATE(A6,3),"mmm")))

    which can be shortened to:

    =TEXT(EDATE(A6,CHOOSE(B6,0,6,3)),"mmm")

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    How about this then?

    =IF(MONTH(EDATE(A6,CHOOSE(B6,0,6,3)))<MONTH($A$2),TEXT(EDATE(A6,CHOOSE(B6,1,12,6)),"mmm"),TEXT(EDATE(A6,CHOOSE(B6,0,6,3)),"mmm"))


  9. #9
    I'll have to play w/ it a bit, but it looks very good and an even more elegant solution than look-up tables. Thanks a bunch and I'll be back shortly.

  10. #10
    Yup, that seems to be working very well. So much so that it also works in Google sheets as well. I'll be closing the thread there and making sure you're sited as the solution. Thanks a bunch.

Page 1 of 2 1 2 LastLast

Posting Permissions

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