Help with a complex formula or lookup table

wmpwi

New member
Joined
Jan 29, 2014
Messages
6
Reaction score
0
Points
0
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.
 

Attachments

  • lookup reference problem.xlsx
    14 KB · Views: 13

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

wmpwi

New member
Joined
Jan 29, 2014
Messages
6
Reaction score
0
Points
0
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:

wmpwi

New member
Joined
Jan 29, 2014
Messages
6
Reaction score
0
Points
0
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:

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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")
 

wmpwi

New member
Joined
Jan 29, 2014
Messages
6
Reaction score
0
Points
0
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.

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")
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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"))
 

wmpwi

New member
Joined
Jan 29, 2014
Messages
6
Reaction score
0
Points
0
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.
 

wmpwi

New member
Joined
Jan 29, 2014
Messages
6
Reaction score
0
Points
0
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.
 
Top