excel sheet that I have to calculate has following fields
year 10 # 11# 12# 13# 14
values 2.13# 2.31# 3.90# 2.70# -0.3
I have to calculate
(1)numerical(NORMAL) difference between year and average (means average of values of 10,11,12,13,14(and so will be 5,9,13,17,21,25
(2)Percentile difference between year and average
(3)numerical(NORMAL) difference between year and previous year(means values of 11-values of 10)
(4)Percentile difference between year and previous year
And all that is needed in ROUND(calculated value,2) with + OR - sign
Format : @<YEAR/QUARTER_AS_APPLICABLE>normal_value#$#Percentile_value(for average)-*-normal_value#$#Percentile_value(for Previous year)
This four fields has to be clubbed in one cell (I am using &)
For indicative purpose-----------------------
There will be 18 calculations
(1,2,3,4,)year11-10
(5,6,7,8)year12-11
(9,10,11,12)year13-12
(13,14,15,16)Year14-13
(17,18)average-14----so it is to be just in 5 cells.
For indicative purpose---------------------
Another version is Quarters
Dec-12# Mar-13# Jun-13# Sep-13# Dec-13# Mar-14# Jun-14
34.4 # 31.4# 40.3# 44.7# 45.8# 46.2# 46.9
For indicative purpose-------------------------
There will be 26 calculations
(1,2,3,4,)Mar13-Dec12
(5,6,7,8)Jun 13-Mar-13
(9,10,11,12)sep13-jun13
(13,14,15,16)dec13-sep13
(17,18,19,20)mar14-dec13
(21,22,23,24)jun14-Mar14
(25,26)average-jun14----so it is to be just in 7 cells.
For indicative purpose---------------------------