Smoothing data across columns

[Mayzy4186]

I have a component lifecycle model built which plots out the quarter by quarter replacement costs for various items. This data is plotted out to the approriate year and then divided into 4 to give a quarterly price. I now need to smooth or spread the profile across the 160 quarters and spill the dollar amounts into the following year without changing the overall total. Below is picture showing how the data is currently plotted and how id like it to look after. (in this instance the annual value is spread across 8 qtrs instead of 4) This also shows how the orginal calc worked to populate these fields.

There are 6000 rows and 160 columns

the original formula is below as is the fields picked up by the forumla, this is repeated across the model with 160 QTR's

=IF(AD$7=$V10,$T10,IF(AD$7=$W10,$T10,IF(AD$7=$X10,$T10,IF(AD$7=$Y10,$T10,IF(AD$7=$Z10,$T10,IF(AD$7=$AA10,$T10,IF(AD$7=$AB10,$T10,IF(AD$7=$AC10,$T10,0))))))))/4

See below for data to be smoothed.

See below for example of smoothed data (done manually)

Any guidance would be appreciated.

 

[kvsrinivasamurthy]

Not understood what you want. Any way the formula can be simplified as below.

=If(countif($V10:$AC10,AD$7)>0,$T10,0)/4

 

[Mayzy4186]

Not understood what you want. Any way the formula can be simplified as below.

=If(countif($V10:$AC10,AD$7)>0,$T10,0)/4

That works to simplify the initial data so thankyou but i need a way to smooth the values created by using that formula.

I need the value @$T10 for example to be spread across mutliple years (2034,2035 etc) not just 2034 like in the screenshot. For context, the total quarterly values go into a table like the below and it has a very lumpy profile.

 

[kvsrinivasamurthy]

Is it possible to upload the sample file showing the expected result. explain how to get the required result.
take the help of some website which can host to upload file. Give the link here.
"Smooth the values" not understood.