If AND or similar formula to adjust column with periodic changes throughout day

I have a sheet with a list of rates for years 2023-2052. We receive updates periodically throughout the day and the updates come over in bins that are not always the same (2023; 2023-2052; 2034-2046 etc) (also see example on sample book).

Each of these updates could be cuts/bumps to the original rates in the column. I am trying to create an entry where I can select a certain range and enter the period updates in a cell to update the original column based off of how much it went down or up. Right now I have a simple IF AND formula and need some guidance on which direction to go in.

Please refer to sample excel sheet to help further explain visually