Results 1 to 7 of 7
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    shifting from absolute to relative formulas (Excel 2003)

    I'm at a loss as to what to call the subject of this post... I have a large financial model that is generic (which is turning out to be much harder to do than a specific one). It's generic in that the user can enter 1-7 service names each of which has 1-5 sub-services (and there are other 'n' items to make it more <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>). Each of these services can start in any year and take from 1-5 years to complete. My problem is how can I create some formulas that know what year they are to start in, and be 0 in other years.

    For example: Service1 starts in 2006 and duration = 3 years, Service2 starts in 2007 and duration = 2 years. I have various capex, opex, arpu, etc. data that needs to be valid (non-zero) only during the years the service is "online" (2006-2009 for Service1, 2007-2009 for Service2 in my example).

    I've attached a simplistic workbook with just one example. The start year and duration can change (blue cells) but I can't figure out formulas that track against these years w/o losing data. I manually entered the data I want but if you change the years and/or duration, they don't work. It seems I can't just create a simple IF formula and drag it across since I'll lose reference to previous cells. I'm sure this doesn't make sense but I can't explain it very well. If I have a formula like:

    =IF(and(yr>=2006,yr<=2008),"fetch data in some cell",0)

    when I drag this type of formula to the right to fill the years (7 total), the cell address in the TRUE part changes of course but, I need it to stay still (as if it was an asbolute formula like $C$4) until the condition is TRUE at which point it should change to a relative formula like C$4. Clear as mud, eh? <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15> I do not know in advance the year the user will start with so I need formulas that are smart enough to 'track' with the year chosen. I hope this doesn't need VBA as I am too close to a deadline to add code (and this is a very large model).

    Hopefully the attached workbook will do a better job of explaining my problem.

    Deb <img src=/S/please.gif border=0 alt=please width=31 height=23> <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15>

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shifting from absolute to relative formulas (Excel 2003)

    Your TRUE/FALSE flags work OK. They are equivalent to 1 and 0 respectively. You could just multiply your calculation by the TRUE/FALSE cell to suppress it when the flag is false - or have I completely missed the point? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: shifting from absolute to relative formulas (Excel 2003)

    Try this formula in D8:
    <code>
    =IF(D7=$K7,$B7,IF(D7<$K7+$L7,C8*(1+$B8),0))
    </code>
    It can be filled to the right, and it can be copied to D13 provided that you make the relative positions of the cells uniform (they aren't in the workbook you attached.) See attached version.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: shifting from absolute to relative formulas (Excel 2003)

    <hr>I have a large financial model that is generic (which is turning out to be much harder to do than a specific one)<hr>

    They always are. The people with the least experience in modeling or analysis are always the ones to say "I think we should have a 'standard' model for all our projects..." - to which the only correct response is something along the lines of "Sure; just show me the standard project and I'll get right on it."

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shifting from absolute to relative formulas (Excel 2003)

    <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15> <img src=/S/groan.gif border=0 alt=groan width=16 height=15> All too true! Where were you in Nov when I got "volunteered" to do this? So let's have a tool that has 'n' legacy services, and 'n' new services (each with 'n' sub-services) where each service can start/end in any year (and all the corresponding pricing, fees, equipment, etc. have to begin in the right year). Now make one income statement that can show all these unknowns, and and and ..... <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

    Thanks for the sympathy.
    Deb

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shifting from absolute to relative formulas (Excel 2003)

    Hey this is great, it works for the example I gave. It's <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15> but I think I can adapt it for the other 1000+ similar data rows I have across 15 sheets. I definitely wouldn't of figured that one out by myself, no way. You understood my problem very well <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Potentially the user of this tool can have 17 different services each of which has their own start date and duration but I have one income statement which has one time span (10 yrs) so I needed to dynamically put the right $ in the right year column for each service (can't assume all start at the same year). It lets them do what-ifs on the economics of turning off legacy network services and turning on (migrating to) new services as replacements so I need the flexibility of changing how long this transition takes (and the $ impact of doing it in 2 years vs 7 years, for example).

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Deb

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shifting from absolute to relative formulas (Excel 2003)

    Yeh that's what I initially thought too and it works if everyone starts at the same year. The T/F flags at top are used to conditionally format the year headings to make it visually clearer what years are involved for the analysis. Hans was able to figure out a formula for me to dynamically decide where to place the first $ given that it needs to track with the starting year (which is a variable, not fixed). That's what I was trying to explain in saying it needed to start off as absolute then change to relative once it saw TRUE for the year.

    This isn't a common use since I think most models use a fixed timeline but mine isn't known up front and not all costs/income starts at the same time.

    Thnx, Deb

Posting Permissions

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