+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    09-29-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    7

    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
    Last edited by jonnym28; 11-23-2015 at 07:13 PM.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    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: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  4. #4
    Registered User
    Join Date
    09-29-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    7

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

    Quote Originally Posted by sandy666 View Post
    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
    Last edited by jonnym28; 11-23-2015 at 09:41 PM.

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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
    Quote Originally Posted by jonnym28 View Post
    (...)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.
    Last edited by sandy666; 11-24-2015 at 06:17 AM. Reason: formula update

  6. #6
    Registered User
    Join Date
    09-29-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    7

    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. #7
    Registered User
    Join Date
    09-29-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    7

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

    Quote Originally Posted by sandy666 View Post
    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.
    Quote Originally Posted by mehmetcik View Post
    Paste into B4 and fill down
    change the second part of the formula to meet your requirement

    Formula: copy to clipboard
    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. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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. #9
    Registered User
    Join Date
    09-29-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    7

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

    Quote Originally Posted by sandy666 View Post
    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. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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. #11
    Registered User
    Join Date
    09-29-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    7

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

    Quote Originally Posted by sandy666 View Post
    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.
    Attached Files Attached Files

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool 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. #14
    Registered User
    Join Date
    09-29-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    7

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

    Quote Originally Posted by sandy666 View Post
    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. #15
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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
    Last edited by sandy666; 11-25-2015 at 12:01 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need a formula to repeat values in column A a certain number of times
    By saeedaltaf in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-14-2015, 05:45 AM
  2. Replies: 1
    Last Post: 07-17-2015, 02:56 AM
  3. Replies: 4
    Last Post: 03-08-2014, 08:01 AM
  4. [SOLVED] Repeat a formula 5 times before changing cell reference
    By amartin575 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-28-2014, 04:11 PM
  5. Automatically repeat four times with a formula vertically
    By Enda80 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-05-2014, 11:47 AM
  6. [SOLVED] VBA: repeat a formula a number of times
    By dinahajjar in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-24-2013, 11:03 AM
  7. repeat something n times formula
    By cro4you.org in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2008, 11:14 AM

Bookmarks

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