# Thread: IF function? (Office XP)

1. ## 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

2. ## 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. ## 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.

4. ## Re: IF function? (Office XP)

Louise,

I have attached a workbook with only the demo sheet.

5. ## 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!

6. ## 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. ## Re: IF function? (Office XP)

Thanks Hans! It was very helpful!

#### Posting Permissions

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