Need a FORMULA expert please!!

    Hi,
    I am trying to join 2 IF statements together and my patience is growing thin with this one. If you want to duplicate my efforts:
    C13 - effective date
    C18 - 1st month of 12 months, i.e. 1/1/03 (term will almost never be a calendar year)
    D13 - value if true
    "-" - value if false


    1st statement
    =IF(((MONTH($C13)/12)+YEAR($C13))<=((MONTH($C$18)/12)+YEAR($C$18)),$D13,"-")


    B13 - end date
    B18 - last month of 12 months, i.e. 12/31/03 (term will almost never be a calendar year)
    D13 - value if true
    "-" - value if false

    2nd statement
    =IF(((MONTH($B13)/12)+YEAR($B13))<=((MONTH($B$18)/12)+YEAR($B$18)),$D13,"-")


    I am trying to automatically calculate 12 months (each in its own cell) based on the 'start date' and 'end date'.


    For the 2nd months formula: add +1


    =IF(((MONTH($C13)/12)+YEAR($C13))<=((MONTH($C$18)+1)/12)+YEAR($C$18)),$D13,"-")


    3rd month add +2 and so on.


    If ANYONE gets what the heck I am babbling about, I would appreciate the help!


    thanks,
    kt

    Errrr, 'calculate 12 months' isn't very descriptive and i'm not up on excel enough to be able to read the code and decipher it in my head...
    A good tip about nesting if statements and formulas tho :-
    Find some space on the worksheet and write each individual formula into it's own cell and confirm they all work. Once you've done that you can construct the main formula in it's correct cell location by copying and pasting in the individual cell formula's.


    eg
    =if(dummy1&gt;dummy2,dummy3,dummy4)


    substitute for the real thing


    Not sure if you'll get my drift.. Somewhere on this website, it explains the technique much better only i forget where exactly

    Kt,


    I am finding it hard to follow your requirement.


    Coud you re-structure your question as follows please.


    1.Input cells (with examples)
    2.Desired result from inputs.


    e.g.


    1.Inputs = A1 = 1, B1=1
    2.Desired output = C1 = 2



    Thanks,
    Rennie

    Yeah, me too! It seems my needs are not very general. I'm not positive what you requested, but maybe this explanation may help?


    The formula is used in a form of pricing that is spread out over 12 months and not just 1 flat price.
    So let's say I charge you $12,000 per year for product X, but I'm going to break it out into 12 months. Now, I have 12 months broken out and each cell equals $1000. Then, 6 months later you want to add 1 more X item at $12,000. I need to add the $1000 price starting at the 6th month and ending at the last month. I have the first 6 months equaling a "-".
    This is the first formula.


    I need to do the same for the 2nd formula, which is the X item could start on the first month, then stop on the 6th month.
    The first 6 months would be $1000 each and the last 6 months would be "-".


    Thanks,
    KT

    Katie,


    My thick brain can not still understand what is required. I will keep working on it and get back to you.


    But I have pasted an extract from excel help that may help you.


    Quote


    I am trying to join 2 IF statements together



    Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.


    If AverageScore is Then return
    Greater than 89 A
    From 80 to 89 B
    From 70 to 79 C
    From 60 to 69 D
    Less than 60 F



    You can use the following nested IF function:


    IF(AverageScore&gt;89,"A",IF(AverageScore&gt;79,"B",
    IF(AverageScore&gt;69,"C",IF(AverageScore&gt;59,"D","F"))))
    In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average&gt;89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.


    I am sorry if you have already seen this.


    Thanks,
    Rennie

    Rennie-
    You have at least proven to me that the formula can handle the logic I am trying to accomplish...


    I guess my biggest issue is getting the dates down correctly. I want each month's formula to work independently.


    I'll try and come up with a better explanation if I can.
    Thanks for your help!


    Paddyd-
    Good info, but the pricing isn't the issue, it's each cell that represents a date/month/year knowing when to do what.


    Katie:D

    Rennie


    Take a look at the attachment now. I just can't seem to join the 2 formulas together. I am probably doing something stupid with the (), but this is driving me insane!

    this is all getting a little convoluted for me (only just back from the pub), but I think you are over-complicating matters.


    1) If you've got proper date values (which you have), there's no need to parse them again with the date() function. In general, as simple


    =if(a1>=b1,do_something,do something else)


    should suffice.


    2) there are native excel functions that could help here. eg add one month to a date:


    =edate(a1,1)


    will return the date one month forward from the date in a1. (you will need to install the analysis tool pack for this - go to tools | addins).


    3) if you're trying to combine the 2 conditions, an 'and' step might be enough:


    =if(and(date>=start_date,date<=end_date), do something, do something else)



    Does this help? If not, post back & I'll take a proper look at your file.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login