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

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
mcthomas00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NBVCCommented:
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
Rob HensonFinance AnalystCommented:
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
mcthomas00Author Commented:
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
NBVCCommented:
Starting in G30, try:

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

copied across.
0
Rob HensonFinance AnalystCommented:
Did you try my suggestion, this would put zero in columns if calculated beyond 2042?

Thanks
RH
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.