Repeat formula X times - once X times has been achieved then Y - repeat X again

1. Repeat formula X times - once X times has been achieved then Y - repeat X again

Hi all,

I am trying to calculate a lease schedule that goes on indefinitely but I am having trouble with the formula I need.

The situation is as follows, but a simplified one
• I have 10 leases, all ending at different dates
• I already have a formula to calculate rent X*Y for each lease
• once the original leases end, I want a void month
• This is when it gets tricky
• I want to assume that here on in, all leases are 12 months
• therefore I want to do X*Y 12 times
• after 12 times, I want to have a void month
• and repeat X*Y for another 12 months

I can't figure out how to loop the X*Y for 12 times and have a void. Could anyone help me without vba please?

Thanks

Jonny

2. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

Paste into B4 and fill down
change the second part of the formula to meet your requirement

Formula:
`Please Login or Register  to view this content.`

3. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

is that what you want?
``Please Login or Register  to view this content.``
1-12,blank,1-12,blank...etc.
no matter where you put this formula. it will start from 1 always with sequences like above.

4. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

Originally Posted by sandy666
is that what you want?
``Please Login or Register  to view this content.``
1-12,blank,1-12,blank...etc.
no matter where you put this formula. it will start from 1 always with sequences like above.
Thank you Sandy - this is essentially what I'm looking for. The only exception however is that rather than 1-12, I would like to have something like \$C12*U\$5 going across the columns. The reason why I need to have two cells multiplying each other is because C12 is the rental value while U5 is inflation - so the actual value changes, but the formula stays the same (if that makes sense). This formula would be perfect if it was going across columns, rather than down rows - apologies I should have specified - and referencing the formula mentioned.

and instead of blank, I would want a cell reference - but I'm assuming I would simply put this cell reference in between the only set of ""?

Many thanks

Jonny

5. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

IF(....)=13,"your reference",(...the rest) without quotes (or if it will be a text - with quotes )

try
``Please Login or Register  to view this content.``
but formula counting like I said before: 1-12 and blank, where now blank is your reference and show result of C12*U5

without any example I can't say more because I don't see how you organize you worksheet

btw. with mehmetcik's formula you can do the same: =IF(MOD(ROWS(A\$1:A1),13)=0,"your reference",C12*US)
you need to know what are you doing with "red" part of formulas.

I reading very fast and lost it, sorry
Originally Posted by jonnym28
(...)This formula would be perfect if it was going across columns, rather than down rows - apologies I should have specified (...)
with mehmetcik's, (modified) formula, again (thanks mehmetcik )
=IF(MOD(COLUMN(A\$1),13)=0,"reference",\$C12*U\$5) and drag it to the right
or
``Please Login or Register  to view this content.``
and drag to the right also.

6. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

Thank you for the reply both (apologies mehmetcik, didn't see your reply) I will have a go and see if it works otherwiseiI'll post an example

7. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

Originally Posted by sandy666
IF(....)=13,"your reference",(...the rest) without quotes (or if it will be a text - with quotes )

try
``Please Login or Register  to view this content.``
but formula counting like I said before: 1-12 and blank, where now blank is your reference and show result of C12*U5

without any example I can't say more because I don't see how you organize you worksheet

btw. with mehmetcik's formula you can do the same: =IF(MOD(ROWS(A\$1:A1),13)=0,"your reference",C12*US)
you need to know what are you doing with "red" part of formulas.

I reading very fast and lost it, sorry

with mehmetcik's, (modified) formula, again (thanks mehmetcik )
=IF(MOD(COLUMN(A\$1),13)=0,"reference",\$C12*U\$5) and drag it to the right
or
``Please Login or Register  to view this content.``
and drag to the right also.
Originally Posted by mehmetcik
Paste into B4 and fill down
change the second part of the formula to meet your requirement

Formula:
`Please Login or Register  to view this content.`

Thank you both - I have tried this and it almost works!

It is difficult for me to attach an example as it is confidential data but I will try my best to explain what is happening.

I don't think the previous if functions really matter, so I won't outline them. So.......
• I have leases which expire at random points
• After each lease, if the date (of current column) is equal to expiry + 1, then -200 ------- simple so far
• my next problem is:
• For all other conditions where date <> lease expiry + 1, execute mehmetcik's formula

This however poses problems because mehmetcik's formula doesn't recognise where it is first executed and it is not variable (if that makes sense)

So I have a problem where every 13th month, no matter when the new lease started, I will have a void of -200.

Ideally, this -200 needs to appear, 13 months AFTER mehmetcik's formula is first executed.

Thank you so much for your help guys, really appreciate it.

8. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

is it something like this? (dummy data)

xyz-jonny.xlsx

9. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

Originally Posted by sandy666
is it something like this? (dummy data)

Attachment 432005
I am not sure how to upload a document so unfortunately here is a screenshot of what I would like to achieve. Is there a way to achieve this using Mehmetcik's formula (purely to shorten the total formula)?

Once again, thank you for your time.

ex.PNG

10. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

1. go to Advanced (at the bottom of new post) then click paper clip

2. I don't see any "red" in picture
3. before you says: -200 not void

11. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

Originally Posted by sandy666
1. go to Advanced (at the bottom of new post) then click paper clip

2. I don't see any "red" in picture
3. before you says: -200 not void
Apologies, I forgot to highlight the red.

-200 is the same as void (i just put down void as i was trying to explain the file in words)

I will upload a proper version now.

12. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

OK, got it.
I'll look at this a little later, in a meantime maybe someone else will give you faster and good advice

13. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

Check this (I'm not sure about horizontal version but the rest is up to you )

xyz-jonny-HV-sandy.xlsx

14. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

Originally Posted by sandy666
Check this (I'm not sure about horizontal version but the rest is up to you )

Attachment 432150
thanks Sandy, this is great! really appreciate your time and help.

In case you're interested, I am building a cash flow hence the annoying need for the horizontal format :P

15. Re: Repeat formula X times - once X times has been achieved then Y - repeat X again

IMHO it always can be recreated to vertical : )
if it resolved your problem don't forget mark thread as solved. Thank you

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1