?
Solved

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

Posted on 2014-07-24
5
Medium Priority
?
190 Views
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)
Year-maintenance-report.xlsx
0
Comment
Question by:mcthomas00
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 40216833
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:

=IF($B$19+$C$30<2042,$B$19+$C$30,0)

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...

=IF($B$19+$C30<2042,$B$19+$C30,0)
0
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 1336 total points
ID: 40216867
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:

=IF($B$19+(F$29*$C30)>$B$19+$B$22,0,$B$19+(F$29*$C30))

in F30 and then copy across as far as required.

Thanks
Rob H
0
 

Author Comment

by:mcthomas00
ID: 40216873
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
Year-maintenance-report.xlsx
0
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 664 total points
ID: 40216892
Starting in G30, try:

=IF(MAX($F30:F30)+$C$30<2042,F30+$C$30,0)

copied across.
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 1336 total points
ID: 40217064
Did you try my suggestion, this would put zero in columns if calculated beyond 2042?

Thanks
RH
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
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…

864 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