Distribute value to ranked records

I have a list of road sections along with the year they were each constructed and their area. They all need to be renewed in 2018 but as we can't afford to do this we need to spread it over 5 years. The sections that get renewed in each of the 5 years then need to have that particular year given to them as the new construction year. I hope the image below helps. Sorry it isn't a table but I can't find any instructions on how to use tables in the forum.

Capture.JPG

Ideally the oldest surfaces should be done first and the newest last. Another difficulty is that the area per year to be assigned won't match the area of discrete road sections. It doesn't matter if the area renewed each year varies to match the individual section areas as long as the total over the five years remains the same. I am trying to get one of the outputs shown below.

Capture.JPG

I hope that is clear enough.

Thanks,
Chris

Hi Chris,

Originally Posted by cep32

I hope the image below helps. Sorry it isn't a table but I can't find any instructions on how to use tables in the forum.

Actually it's even better if you upload your sample file (as everyone else does) so the helpers don't need to retype the data. To do this, hit the Go Advanced button, then hit the Manage Attachments button, and then you can upload your file.

For your case, we need a helper column which content is unique values of Construction Year.
Please refer to my attached file. Construction Year is located at Column A, the helper column is at column F.

Array formula at cell F2 :

=IFERROR(INDEX($A$2:$A$7, MATCH(0, COUNTIF($F$1:F1, $A$2:$A$7), 0)),"")

end this formula by hold and press Ctrl-Shift-Enter all at once.

Copy down this array formula to F3:F7.

We can now calculate the New Construction Year, the formula at cell C2 :

=RANK(A2,F$2:F$7,1)+(2018-1)

Regards