Nested conditions (Excel 97)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.)

Reply | Quote

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

=IF(MOD(D$2-1,$C3)=0,$A3*$C3/12,0)

becomes

=IF(MOD(MONTH(D$2)-1,$C3)=0,$A3*$C3/12,"")

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

Reply | Quote