I need to perform a calculation and have it adjust in the next cell based on the previous cell

Posted on 2014-07-24
Last Modified: 2014-08-14
I need to perform a calculation and have it adjust in the next cell based on the previous cell

This is the formula I am using:  =IF($B$19+$C$30<2042,B19+$C$30,0), the problem is when I copy to the next cell it gives me the value 12 because the returned value is 0 + 12 = 12, but the value should be 0.  See sample

(see attached spreadsheet)
Question by:mcthomas00
    LVL 23

    Expert Comment

    It looks like you also need to make B19 absolute, ie turn it in $B$19 so it maintains the reference to the year in B19:


    although I am not sure the significance of copying the same formula/result across the cells ?!?

    And perhaps you need to remove the $ from before the 30 in $C$30 so that you can copy down and get reference to C31, C32, etc...

    LVL 31

    Assisted Solution

    by:Rob Henson
    Looks like you are wanting to increment the year value in row 30 by the number of years in C30 up to a max year of 2042 which I take it you are getting from start year (B19) plus expected life (B22).

    If so try this:


    in F30 and then copy across as far as required.

    Rob H

    Author Comment

    Copying across are years where I have the 1 2 3 4 5 6 across
    See updated sheet and formula change
    Problem occurs in column #4, 5, 6, etc.  The values should appear as 0 but because the result is less than 2042 it adds the 12 to the 0 giving the results of 12 24 36 48.  I need it to calculate if the result is 0 in the previous cell then it should not sum the requency routine maintenance values thus resulting in a 0
    LVL 23

    Assisted Solution

    Starting in G30, try:


    copied across.
    LVL 31

    Accepted Solution

    Did you try my suggestion, this would put zero in columns if calculated beyond 2042?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
    A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now