Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF function? (Office XP)

    Hi,
    Hopefully I can explain what I need correctly. I need to calculate quarterly dues based on a date in a cell. It's either going to be the full amount (if it's the first month of the quarter) or 1/3rd or 2/3rd of the total depending on which month they joined.

    I think that I have a couple of ways to go about it. I was thinking of using the IF function but I don't think I can spread it out 12 times. In other words, "IF" it's "March" do this, "IF" it's "April" do this, "IF" it's "May" do this .......etc. but I don't think Excel would allow such a long formula.

    The second thing I thought of is to somehow tell it "IF it's "March, June, Sept or Dec" do this...........etc. but I don't know how to do that and make it work.

    Any ideas?
    Thanks
    Louise

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

    Re: IF function? (Office XP)

    Hi Louise,

    Say that the date is in cell A1, and that the full quarterly fee is in cell E1. The following formula will calculate the amount due:

    =(1-MOD(MONTH(A1)+2,3)/3)*$E$1

    If you have more dates in A2, A3 etc., you can fill the above formula down as far as needed.

    Explanation: the MOD (modulo) function calculates the reminder of the first argument when you divide it by the second argument. The following table shows how the fraction is calculated in a number of steps; the formula does this in one step.

    <table border=1><td colspan=2>Month</td><td>Month+2</td><td>Remainder</td><td>Remainder/3</td><td>1-Remainder/3</td><td>Jan</td><td align=right>1</td><td align=right>3</td><td align=right>0</td><td align=right>0 </td><td align=right>1 </td><td>Feb</td><td align=right>2</td><td align=right>4</td><td align=right>1</td><td align=right> 1/3</td><td align=right> 2/3</td><td>Mar</td><td align=right>3</td><td align=right>5</td><td align=right>2</td><td align=right> 2/3</td><td align=right> 1/3</td><td>Apr</td><td align=right>4</td><td align=right>6</td><td align=right>0</td><td align=right>0 </td><td align=right>1 </td><td>May</td><td align=right>5</td><td align=right>7</td><td align=right>1</td><td align=right> 1/3</td><td align=right> 2/3</td><td>Jun</td><td align=right>6</td><td align=right>8</td><td align=right>2</td><td align=right> 2/3</td><td align=right> 1/3</td></table>
    The reference to cell E1 is absolute (this is the function of the $ characters), so that it won't change when you fill down.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF function? (Office XP)

    Hi Hans,
    I knew you would know! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> I'm going to have to study what you are saying. Right now, it's way beyond me! <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>
    In the meantime, I (hopefully) have attached a very small spreadsheet of what I'm looking at. If you can see it, do you think your formula will work with it? Our year-end is Februay so our quarters start with March, June, Sept and Dec.

    Thanks once again for all your help.
    Louise

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

    Re: IF function? (Office XP)

    Louise,

    I have attached a workbook with only the demo sheet.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF function? (Office XP)

    Hans,

    Thank you very much! As you know, it works beautifully! I still don't understand it, but it works!

    I was looking at the help files and comparing it with what you had layed out in the earlier post but I must be dense. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Will have to study it some more and hopefully something will sink in so it will be sense to me.

    Thanks again!
    Louise

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

    Re: IF function? (Office XP)

    I have attached a version of the same workbook with all intermediate steps in the calculation in separate columns. Perhaps it will help you understand how it works.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF function? (Office XP)

    Thanks Hans! It was very helpful!
    Louise

Posting Permissions

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