Abstract

You need to smoothen inner-period values, keeping the book-end values and the overall (total) average?

Two simple approaches:

smoothing_inner_period_values

Define these names:

Name Refers to
All_Values =Sheet1!$A$2:$K$2
Bookvalue_End =Sheet1!$L$2
Bookvalue_Start =Sheet1!$A$2
Inner_Count =(Total_Count-2)
Inner_Values =Sheet1!$B$2:$K$2
Total_Average =AVERAGE(All_Values)
Total_Count =COLUMN(Bookvalue_End)-COLUMN(Bookvalue_Start)+1

If all inner values should be identical, enter into
A3: =Bookvalue_Start B3: =(@Total_Average*@Total_Count-Bookvalue_Start-Bookvalue_End)/@Inner_Count and copy to the right until cell K3
L3: =Bookvalue_End

If all inner values (with the exception of one or more most extreme values) should be identical, enter into
A3: =Bookvalue_Start
B3: =IF(ABS(@Total_Average-MAX(Inner_Values))>=ABS(@Total_Average-MIN(Inner_Values)),IF(B$2=MAX(Inner_Values),B$2,(@Total_Average*@Total_Count-Bookvalue_Start-Bookvalue_End-MAX(Inner_Values)*COUNTIF(Inner_Values,MAX(Inner_Values)))/(@Inner_Count-COUNTIF(Inner_Values,MAX(Inner_Values)))),IF(B$2=MIN(Inner_Values),B$2,(@Total_Average*@Total_Count-Bookvalue_Start-Bookvalue_End-MIN(Inner_Values)*COUNTIF(Inner_Values,MIN(Inner_Values)))/(@Inner_Count-COUNTIF(Inner_Values,MIN(Inner_Values))))) and copy to the right until cell K3
L3: =Bookvalue_End

Download

Please read my Disclaimer.

smoothing_inner_period_values.xlsx [17 KB Excel file, open and use at your own risk]