Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Membership Formula (Access 2003 SP2)

    I am trying to work out a formula for membership renewal fee with the following parameters:
    (A) Companies with over 12 months pay full fee per member;
    ([img]/forums/images/smilies/cool.gif[/img] A higher number of members allows a lower rate per member eg 1 member = $500/yr, 2 members = $980/yr, 3 = $1440/yr, etc, 6 = $2700/yr and so on;
    Companies with memberships less than 12 months pay pro-rata fee per member based on point B.

    The problem is where a company has a combination of several members longer than 12 months and others less than 12 months. For example, if XYZ has 3 members with greater than 12 months membership, plus another 3 with only 10 months membership, how much is the company charged? Based on this scenario, it would be $2640 ($1440+$1200). If there were six long term members, the fee is $2700. After that, it seems to be a disadvantage to have any members for less than 12 months in addition to members longer than 12 months. Eight long term members pay $3440 altogether whereas 4 long term plus 4 ten month members pay $3447!

    Has anyone come across this before? Any ideas on how to construct a formula?

    Thanks
    Nick

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

    Re: Help with Membership Formula (Access 2003 SP2)

    Do you really want to calculate the rate for each group separately? Another way would be:

    Company has 8 members, so yearly rate per member is $500 - 7 * $10 = $430.
    If these, 4 are long term members, and 4 have been a member for 10 months, so total fee is 4 * $430 + 4 * (10/12) * $430 = $3153.33

    Does that sound reasonable?

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Help with Membership Formula (Access 2003 SP2)

    I suppose Felton should answer this, but from my reading of his post the fee for 8 long term members would be:

    500 + 490 + 480 + 470 + 460 + 450 + 440 + 430 = 3720

    This is an Arithmetic Progression with a first term of 500 and a Common Difference of -10 and 8 terms , so the total fee for n long term members is:

    (n/2) * (2 * 500 - (n-1)* 10) (n/2)* (2a +(n-1)d)
    Regards
    John



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

    Re: Help with Membership Formula (Access 2003 SP2)

    I read it differently, so I agree that Felton will have to tell which version is correct.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Membership Formula (Access 2003 SP2)

    Thanks for your prompt and thoughtful responses. Sorry for the ambiguity but nevertheless, your answers got me thinking.

    If the company only had 8 long term members, the fee would be $3440. If there were only 8 short term eg 10 month members, the fee would be 3440*10/12, that is, $2867.

    However, it was the combination of both long and short that messed with my brain. So, I restarted my calculations considering 4 long term and 4 ten month members still equates to 8 members altogether.

    If I take the full 8 member fee of $3440 and proportion it to the 4 full time members, I get $1720. Then I take the same full 8 member fee and proportion it to the 4 ten month members on a pro-rata rate, I get $1433.33 ie 3440*(4/8)*(10/12).

    The total of those two portions gives me $3153.33 which works out to be 92% of the original full 8 member fee of $3440. That answer seems workable.

    Thanks again for your time and being a sounding board.

    Cheers

Posting Permissions

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