+ Reply to Thread
Results 1 to 11 of 11

adjusting if/match formula to allow positive and negative movement

  1. #1
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    adjusting if/match formula to allow positive and negative movement

    This is slightly difficult to explain without showing the file itself so I have attached an example of what I am trying to do.

    I have this file with a bunch of items in row 2, some quantities associated with those items in row 3, an installation rate for each of them in row 4, and a lag between each activity in row 5. We have a timescale down the left in 30min increments (for the purposes of this, the timescale has no relevance anyway).

    I have populated a formula in cells E6 to S208 which basically draws a programme for me based on the quantity and the installation rate of each activity, where by default the next activity starts as soon as the previuos one is completed, but the lag in row 5 can be used to put some space in between the activity and the one before it. e.g. if you look at activity 5, the lag of 2 has resulted in this activity starting 2 cells AFTER the previuos activity has finished.

    The issue is related to this, my formula only works for positive lag, to put a delay between 2 activities, but what I also need it to do is be able to have negative lag, i.e. have the next activity start x rows BEFORE the end of the prevuios activity and my current formula falls down if I enter a negative number in row 5, it simply starts the activity from the end of the previous one.

    Is it possible to adjust it slightly so that it works with both negative AND positive numbers in row 5? I can make it do one or the other, but have not been able to get it to do both within the same formula.

    thanks
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: adjusting if/match formula to allow positive and negative movement

    Hi newwok,

    See the attached file .. for activity 5, I used below formula:-
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter above formula using key combination: ctrl shift enter


    TD Matrix test.xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    Re: adjusting if/match formula to allow positive and negative movement

    thanks it almost works but not quite. If i use a negative number in row 5, it will start on the same row as the previous activity finishes, but never any higher. If i use -1 it has the same effect as using -10 or -50, they always start on the end of the last activity rather than say 5 rows higher up.
    Last edited by neowok; 04-16-2013 at 09:55 AM.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: adjusting if/match formula to allow positive and negative movement

    Not sure.. but I believe, you changed your above post as I got some other notification from your side.. did you edited / deleted your above post ?




    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    Re: adjusting if/match formula to allow positive and negative movement

    Yes just edited my post after playing around some more, it doesnt quite work yet.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: adjusting if/match formula to allow positive and negative movement

    Okay..

    See the logic I used in the array formulas and try incorporating the same for other activities.. thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>

  7. #7
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    Re: adjusting if/match formula to allow positive and negative movement

    My point was, it doesnt work. Using that formula with a negative lag of say -5 does not move the numbers to start 5 rows higher up than the finish of the previous activity, it will move it up to the same row as the previuos activity, but not 5 rows above it, even using -10 lag, it still puts it on the same row as the previuos activity.

  8. #8
    Registered User
    Join Date
    04-14-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: adjusting if/match formula to allow positive and negative movement

    You said you can get it to do it either way but not together right? Just do =if(K5<0, your negative formula, your positive formula)
    If you found this helpful, click the star

  9. #9
    Registered User
    Join Date
    04-14-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: adjusting if/match formula to allow positive and negative movement

    Got it. you need to replace your lookup_array in your two match functions. currently you have the range going from row 6 to the row immediately above the one you are testing, but that needs to be offset by the lag. For instance in I put this in K20:

    Please Login or Register  to view this content.
    I highlighted the changes. If you need more explanation let me know.

  10. #10
    Registered User
    Join Date
    04-14-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: adjusting if/match formula to allow positive and negative movement

    I appologize I fogot to re-make it work with positive numbers. This should work for both cases:

    Please Login or Register  to view this content.
    Again this example can be placed in K20
    Last edited by RS15; 04-16-2013 at 01:15 PM.

  11. #11
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    Re: adjusting if/match formula to allow positive and negative movement

    thanks I think I have managed to make it work with that, probably should have spotted that myself, I was thinking about using offset somehow but couldnt get my head round it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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