Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    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.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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