# Thread: Nested conditions (Excel 97)

1. ## Nested conditions (Excel 97)

I am setting up a budget ws such that in column A we have a \$ amount, in column B a validation box offering Monthly, Quarterly and Semi, and Months 1 in Column C to 12 in column whatever. Now, I want a formula that will slot a calculation amount/12, or amount/4, or amount/2 in the proper columns for a monthly, quarterly or semi-annual election. So a quarterly selection would have 12k in Column A showing 3K in columns 1,4,6 and 9.
Can I get some help in developing and IF formula saying "IF column B says Quarterly AND the Month is 1,4,6,9, THEN amount/4, and so on. My primitive thinking imagines a nesting of this test for the three conditions. Perhaps there is a simpler formula that can be suggested.
If I had a year, I could probably get it myself, but don't count on that.
Thanks!

2. ## Re: Nested conditions (Excel 97)

Attached workbook does what I think you're asking. I added an additional column, "Interval", that translates Monthly, Qtrly, Semi to 1,3,6 respectively, then used that value in the formula.

3. ## Re: Nested conditions (Excel 97)

Thank you very much. I will study the formulas with interest. I have seen the MOD function in use with various issues, including some interesting cell formatting functions, but have not been able to completely grasp its practical meaning. One gets a definition from the help menu, but not meaning, if you know what I mean. Anyway, I'm on my way! Thanks again.

4. ## Re: Nested conditions (Excel 97)

Gregory, Sure is a mess, but the formula is =IF(\$B2="Monthly",\$A2/12,IF(AND(\$B2="Quarterly",MOD(MONTH(C\$1),3)=1),\$A2/4,IF(AND(\$B2="Semiannually",MOD(MONTH(C\$1),6)=1),\$ A2/2,IF(AND(\$B2="Annually",MONTH(C\$1)=1),\$A2,""))))

See attached workbook. HTH --Sam

5. ## Re: Nested conditions (Excel 97)

Thanks for the help. This what I was thinking about. And there's the MOD function again! Gotta figure that thing out!

6. ## Re: Nested conditions (Excel 97)

FWIW you can take Sammy's idea and turn it into pure boolean math like this:

=(\$B2="Monthly")*\$A2/12+(\$B2="Quarterly")*(MOD(MONTH(C\$1),3)=1)*\$A2/4+(\$B2="Semiannually")*(MOD(MONTH(C\$1),6)=1)*\$A2/2+(\$B2="Annually")*(MONTH(C\$1)=1)*\$A2

... a few characters shorter. (Check to see if I changed the spellings on period names Semiannually and Annually.)

7. ## Re: Nested conditions (Excel 97)

I actually like Colin's solution better. I would hide the Interval column and change the month numbers in the header to dates (like I had then), then use the month function in Colin's formulas, ie <pre>=IF(MOD(D\$2-1,\$C3)=0,\$A3*\$C3/12,0)</pre>

becomes <pre>=IF(MOD(MONTH(D\$2)-1,\$C3)=0,\$A3*\$C3/12,"")</pre>

As for the MOD function, it is just the remainder after integer division and is useful for things that happen periodically. In this case, if M is a number between 1 and 12, then mod(M,3) is 1 when M is 1, 4, 7, or 9. Colin used mod(M-1,i) where i is the interval so that the formula would still work in the monthly case. --Sam

#### Posting Permissions

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