Help with a string of IF statements

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.


Capture.PNG

Sample.xlsx

Thanks and apologies I should have thought of that.

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

Apologies for the confusion I only meant to attach one sheet not the whole book and the stressed case was not meant to be included. I have attached one sheet now and here's a better outline of where I'm at:


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.

This worked perfectly, thank you very much, really appreciate it!

Thanks again, apologies, I will mark as solved now! Just one more question if you don't mind, at present the formula displays "Void" if the date in row E is exceeded, is it possible to amend so that it will display "Void" if Row E or Row D are exceeded?

Sorry to re-open this but there are things I would like to amend on this:


Current Formula:
=IF(AND(OR(K$2>$F4,K$2>$E4),OR(K$2<=EOMONTH($F4,$J4*3),K$2<=EOMONTH($E4,$J4*3))),"Void", $G4/4 )

Current Logic:
(If the Qtr date is past the lease expiry date or lease break date) _or_ (the QTR date is <= the lease expiry date, void for #void periods specified in column J)
(the QTR date is <= the lease break date, void for #void periods specified in column J)
If TRUE = VOID
If FALSE = Rent Amount (Column G/4)
_____________________________________________________________________________
Things I would like to add to formula/Expected Results:

• Only include Void periods if break options are exercised, i.e. only include void periods if column D = “Yes”, if not then no void periods should apply.
If column D1 = “Yes”, or if lease expires, we need to be able to define the following:
• Post Void period Expiry Date – rent to be reinstated with a new expiry date specified in column i.
• Post void Rent value is to be reinstated at a level specified in column H.
• Rent is currently pulling into the model before the start date specified in column C – this should only commence on or after the date specified in column C.
• At the moment the model is voiding everything before the expiry date as well as correctly voiding the specified amount of periods per the j column, this needs to be fixed so that the void period only runs for the specified number of void periods.

Note: A Number of cells are being populated by data entered in the “Data Input & Output” sheet.

I understand there is probably quite a bit of work in this and can purchase points and offer them for completion if you guys think that would be more appropriate?

Hi Jete,
Thank you once again for looking at this, it is much appreciated. Apologies for my delayed response, I have been ill.

I have now attached an updated sheet where I have populated row 7 manually with expected outcomes and included comments in each relevant cell in line with same.

I probably didn’t articulate my desired outcome very well in my last post, allow me to clarify the remaining points below:

If column D1 = “Yes”, or if lease expires, we need to be able to define the following:
• Post Void period Expiry Date – rent to be reinstated with a new expiry date specified in column i. Following a void period which has run it’s course – the date per column I will now be the new expiry date. Essentially whether it’s the Lease Break Date (Column E) or Lease Expiry Date (Column F) that occur first, from this date the Void period will be served and following that the new Expiry date per column i should apply from thereon in.[/I][/I][/I][/I][/B]
• Post void Rent value is to be reinstated at a level specified in column H. Following a void period which has run it’s course – the rental amount per column H will now be the new rental amount (this will feed from data input and output). The annualised amount will feed through to the sheet and will need to be divided by 4 to fit the quarterly model.
• The model is now voiding from the correct expiry/break date but voiding an incorrect number of periods (specified per the j column), this needs to be fixed so that the void period only runs for the specified number of void periods. EG, Row 5 – there should be 4 void quarters but instead there are 16.

Thanks again

Hi Jete,

I've ran a few trials of this and it's 99% there, thanks a million.

One small thing I've noticed is when the "lease break date" (Col E) is the same date as the last day in a period (the dates per row 2 - eg: 31/03/2020)the void period does not appear. I have attached an example of same with E6 demonstrating this, when I apply a different date which isn't the last day of a quarter then the model works fine and the void period present itself as it should.

Do you know why this might be?.

Thanks again

That worked Jete, thanks again.

One last thing and I'm sorry to be a pain, when the Post Void Lease Expiry (Column I) is passed the remainder of the row voids rather than voiding for the number of periods specified in column J. Is there any chance you could help with this?.

Is there anyway I can make a financial contribution to you? I really appreciate the help.

Hi Jete, Sorry to re-open this, I have been having an issue with the model whereby the number of void periods presented in Columns K - AD do not correspond with the number specified in Column J. I have attached an example, any help would be greatly appreciated, many thanks.

Thanks Jete, I have set out a bit more detail below and attached a version with expected outcomes:

If "Column D- Break Exercised" is "YES" then - "Void" Periods should apply and follow the Lease Break date (specified in Column E) and Lease Expiry dates (specified in Column F) for the amount of periods specified in "Column J". If "Column D- Break Exercised" is "NO" then no void period period is exercised.

Current Outcome Vs Expected Outcome with current formula.

Current Outcome:
- Row 4: The first void period is correct, 2 void periods as specified in "column J", the second Void period is showing 3 periods instead of 2 and beginning one period early.
- Row 5: The First void period is beginning one period early and is showing two Void periods instead of one as specified in column J. The second void period beginning 30/05/2023 is not showing up.
- Row 6: The two Void periods run into eachother and last for 7 periods rather than 2 seperate Voids for 3 periods each.

Expected Outcome:
- Row 4: Per Spreadsheet Row 7.
- Row 5: Per Spreadsheet Row 8.
- Row 6: Per Spreadsheet Row 9.

Hi Jete, sorry to reopen this thread after so long. Your solution above almost worked. I would be so grateful if you have one more look at the attached - in example 1 - 2 void periods are showing up even though only 1 void period is specified in column J, would you have any idea why this might be? Thanks again so much.

Apologies Jete, You're quite right I was reading it wrong, Thanks again!