Results 1 to 7 of 7
Thread: IF function? (Office XP)

20030901, 21:40 #1
 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?
ThanksLouise

20030901, 21:56 #2
 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:
=(1MOD(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>1Remainder/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.

20030901, 22:24 #3
 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 yearend is Februay so our quarters start with March, June, Sept and Dec.
Thanks once again for all your help.Louise

20030901, 22:41 #4
 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.

20030901, 22:50 #5
 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

20030901, 22:58 #6
 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.

20030901, 23:49 #7
 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