Results 1 to 4 of 4

Thread: Multiple IF Statements

  1. #1
    Neophyte Conorg's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    MO Pro Plus 64-Bit

    Multiple IF Statements



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Click image for larger version. 

Name:	Capture.PNG 
Views:	44 
Size:	62.3 KB 
ID:	9196
    Hi Guys, long story short itís been a long time since Iíve had to use a string of IF statements (probably college). Iíve had an unsuccessful crack at the below but think I just need to brush up on my excel skills in general. In the mean time I would be really grateful if someone might be able to help.

    Essentially, what I want is for the below model to:

    $F$5/4 & IF(H2>$E$4, H4=0, "") - If current date (feeding from H2) is greater than the lease expiry date (E4) then the value of the subject cell(H4-AA4) must equal zero.

    $F$5/4 & IF(H2>$D$4, H4=0, "") - If current date (feeding from H2) is greater than the lease break date (D4) then the value of the subject cell(H4-AA4) must equal zero.

    $F$5/4 & IF(H2=0, "VOID", "") - If the subject cell equals 0 then display "VOID" if not then no effect.

    & IF(D4:G4="VOID", $F$5/4, "") If there are (# per G4's input) x "VOID" periods preceding the subject cell, then the subject cell should revert to $F$5/4 if not then no effect.

    Iím not sure if this is even possible, thanks in advance.

  2. #2
    Neophyte Conorg's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    MO Pro Plus 64-Bit
    Apologies I should have been more clear.


    The results I am seeking are for the time related cells to zero out once they pass their expiry or break date but also to display the word "VOID" when this is the case. Once there has been a consecutive number of "VOID" periods determined by the VOID cell I want the subject cell to revert back to the initial formula of the "Rent" amount divided by 4.


    I appreciate this is somewhat convoluted and if it's not possible I completely understand.


    Thanks

  3. #3
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,656
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please post a sample sheet with some data and expected results ( click Go advanced - Manage attachments) Thx
    Thank you Ken for this secure forum.

  4. #4
    Neophyte Conorg's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    MO Pro Plus 64-Bit
    Sorry I should have attached this initially and here's a better outline of what I am looking to achieve:
    Expected results:
    1. When the current date (H2:AA2) exceeds either the "Lease Break" date (column D) or "Lease Expiry" (Column E) I want the rental values to drop off (or expire as it were).
    2. If the rental values have dropped off and the cell = 0 i want it to display the word "Void" for the amount of quarters specified in column G.
    3. Finally, I want the cashlows/rent to kick back in (at the level it was previously) after the specified amount of "Void" periods (per Column G) have elapsed.


    So I have tried to tackle each of these one at a time as follows:
    1. IF(H2>$E$4, 0, $F$4/4) & IF(H2>$D$4, 0, $F$4/4)
    2. & IF(H4 =0, "VOID", "")
    3. IF(D4:G4="VOID", $F$5/4, "")


    Just to note the model takes the annual rental sum and divides it into quarterly amounts.


    I have attempted each element of the above separately in rows 4-7:
    1. Rows 4 & 5: when the current date exceeds the lease break date or lease expiry date I want the cell to revert to 0, I have these working separately.
    2. Row 6: I can get this working to an extent but I don't know if it is the delimiting or what but the text keeps disappearing (even though I've delimited cells).
    3. Row 7: This appears to be working okay.


    So I have tried (and failed) to merge all of these rules together and it's just not happening for me, any advice would be greatly appreciated.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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