+ Reply to Thread
Results 1 to 4 of 4

Acquisition Schedule Formula

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-01-2010
    Location
    United States, Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    116

    Acquisition Schedule Formula

    I have a row in my life cycle cost estimate (20 year projection) that needs to follow the following command:

    It should be zeroed out every year, except on acquisition years, and every 5th years. Essentially, I would like it so when I change the acquisition years, it will follow the above command.

    for example: (3 year acquisition cycle)

    FY1  FY2  FY3  FY4  FY5  FY6  FY7  FY8  FY9  FY10  FY11
    100    0    0  100    0  100  100    0    0   100   100
    *If i changed the acquisition cycle to 4 years, it would follow:

    FY1  FY2  FY3  FY4  FY5  FY6  FY7  FY8  FY9  FY10  FY11
    100    0    0    0  100  100    0    0  100     0   100
    I would give a dummy book, but I don't even know where to go with this formula...

    Any help would be much appreciated!
    Last edited by shg; 07-19-2010 at 03:13 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Acquisition Schedule Formula

    Here's one way:
           --A-- -B- -C- ------------------------------------D-------------------------------------
       1   value 100 100                                                                           
       2   cycle   3   4                                                                           
       3                                                                                           
       4    FY                                                                                     
       5       1 100 100 B5 and across and down: =IF(OR(MOD($A5, 5) = 1, MOD($A5, B$2) =1), B$1, 0)
       6       2   0   0                                                                           
       7       3   0   0                                                                           
       8       4 100   0                                                                           
       9       5   0 100                                                                           
      10       6 100 100                                                                           
      11       7 100   0                                                                           
      12       8   0   0                                                                           
      13       9   0 100                                                                           
      14      10 100   0                                                                           
      15      11 100 100                                                                           
      16      12   0   0                                                                           
      17      13 100 100                                                                           
      18      14   0   0
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Acquisition Schedule Formula

    My attempt.

    Cheers,
    Attached Files Attached Files
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  4. #4
    Forum Contributor
    Join Date
    07-01-2010
    Location
    United States, Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    116

    Re: Acquisition Schedule Formula

    Wow, they both work, you guys are awesome! ConneX, yours is a bit off on, with the 5yr being 4 on your spreadsheet. I added to your both your reps btw Now to make my life even harder I have differenct aquisition numbers on different schedules?

    Please see dummy book for example.

    Thanks again, and if you need furthur clarification I will be sure to help out.

    *One more thing: I went off of ConneX's workbook because it was just a bit easier to translate to my LCCE...
    Attached Files Attached Files

+ 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