Combining / Reducing Multiple Formulas down to 1 Formula

[GUYNOUK]

In my spreadsheet I am struggling to combine the formulas in order to reduce the columns down.
The Sheet is attached. It's dynamic in that the calculations change if the value in L3 is changed.

The data in Column F (Change) is already availlable on another sheet that I can pull over. So Columns B to E are surplus to requirements and not required.
I want to combine columns G to L so I am left with 3 columns. (1) 'Date' (2) 'Change' (3) the combined G to L columns.
Is this possible?

Thanks

Dynamic-RSI.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	STOCK NAME: XYZ
2	Date	Open	High	Low	Close	Change	Advance	Decline	Average Gain	Average Loss	Relative Strength (RS)	RSI
3												5
4	1-Feb-16	7,589.50	7,600.45	7,541.25	7,555.95
5	2-Feb-16	7,566.65	7,576.30	7,428.05	7,455.55	-100.40	0	100.4
6	3-Feb-16	7,392.45	7,419.40	7,350.30	7,361.80	-93.75	0	93.75
7	4-Feb-16	7,411.45	7,457.05	7,365.95	7,404.00	42.20	42.2	0
8	5-Feb-16	7,418.25	7,503.15	7,406.65	7,489.10	85.10	85.1	0
9	8-Feb-16	7,489.70	7,512.55	7,363.20	7,387.25	-101.85	0	101.85	25.46	59.2	0.430067568	30.07323411
10	9-Feb-16	7,303.95	7,323.45	7,275.15	7,298.20	-89.05	0	89.05	20.368	65.17	0.312536443	23.81163927
11	10-Feb-16	7,264.30	7,271.85	7,177.75	7,215.70	-82.50	0	82.5	16.2944	68.636	0.237403112	19.18559197
12	11-Feb-16	7,203.60	7,208.65	6,959.95	6,976.35	-239.35	0	239.35	13.03552	102.7788	0.126830825	11.25553386
13	12-Feb-16	7,023.65	7,034.80	6,869.00	6,980.95	4.60	4.6	0	11.348416	82.22304	0.138019903	12.12807461
14	15-Feb-16	7,057.35	7,182.80	7,056.80	7,162.95	182.00	182	0	45.4787328	65.778432	0.691392777	40.87712722
15	16-Feb-16	7,201.25	7,204.65	7,037.70	7,048.25	-114.70	0	114.7	36.38298624	75.5627456	0.481493704	32.50055687
16	17-Feb-16	7,058.85	7,123.70	6,960.65	7,108.45	60.20	60.2	0	41.14638899	60.45019648	0.680665926	40.49977546
17	18-Feb-16	7,177.40	7,215.10	7,127.85	7,191.75	83.30	83.3	0	49.57711119	48.36015718	1.025164393	50.6212926
18	19-Feb-16	7,170.55	7,226.85	7,145.95	7,210.75	19.00	19	0	43.46168895	38.68812575	1.123385745	52.90540108
19	22-Feb-16	7,208.85	7,252.40	7,200.70	7,234.55	23.80	23.8	0	39.52935116	30.9505006	1.277179703	56.08603051
20	23-Feb-16	7,240.30	7,241.70	7,090.70	7,109.55	-125.00	0	125	31.62348093	49.76040048	0.635515	38.85717956
21	24-Feb-16	7,075.00	7,090.80	7,009.75	7,018.70	-90.85	0	90.85	25.29878474	57.97832038	0.436349045	30.3790396
22	25-Feb-16	7,029.85	7,034.20	6,961.40	6,970.60	-48.10	0	48.1	20.2390278	56.00265631	0.361394068	26.54588239
23	26-Feb-16	7,039.30	7,052.90	6,985.10	7,029.75	59.15	59.15	0	28.02122224	44.80212504	0.625444043	38.47834971
24	29-Feb-16	7,050.45	7,094.60	6,825.80	6,987.05	-42.70	0	42.7	22.41697779	44.38170004	0.505095068	33.55901422
25	1-Mar-16	7,038.25	7,235.50	7,035.10	7,222.30	235.25	235.25	0	64.98358223	35.50536003	1.8302471	64.667396
26	2-Mar-16	7,321.70	7,380.35	7,308.15	7,368.85	146.55	146.55	0	81.29686579	28.40428802	2.862133552	74.10757587
27	3-Mar-16	7,429.55	7,483.95	7,406.05	7,475.60	106.75	106.75	0	86.38749263	22.72343042	3.801692396	79.17400955
28	4-Mar-16	7,505.40	7,505.90	7,444.10	7,485.35	9.75	9.75	0	71.0599941	18.17874433	3.908960531	79.62908861
29	8-Mar-16	7,486.40	7,527.15	7,442.15	7,485.30	-0.05	0	0.05	56.84799528	14.55299547	3.906274513	79.61793623
30	9-Mar-16	7,436.10	7,539.00	7,424.30	7,531.80	46.50	46.5	0	54.77839623	11.64239637	4.705079132	82.47175934
31	10-Mar-16	7,545.35	7,547.10	7,447.40	7,486.15	-45.65	0	45.65	43.82271698	18.4439171	2.375998371	70.37913263
32	11-Mar-16	7,484.85	7,543.95	7,460.60	7,510.20	24.05	24.05	0	39.86817358	14.75513368	2.701986607	72.98747656
33	14-Mar-16	7,542.60	7,583.70	7,515.05	7,538.75	28.55	28.55	0	37.60453887	11.80410694	3.185716552	76.10922795
34	15-Mar-16	7,535.85	7,545.20	7,452.80	7,460.60	-78.15	0	78.15	30.08363109	25.07328555	1.199828041	54.5419014
35	16-Mar-16	7,457.05	7,508.00	7,405.15	7,498.75	38.15	38.15	0	31.69690488	20.05862844	1.580212972	61.24350933
36	17-Mar-16	7,557.40	7,585.30	7,479.40	7,512.55	13.80	13.8	0	28.1175239	16.04690276	1.75220878	63.66554721
37	18-Mar-16	7,534.65	7,613.60	7,517.90	7,604.35	91.80	91.8	0	40.85401912	12.8375222	3.182391311	76.09023342
38	21-Mar-16	7,619.20	7,713.55	7,617.70	7,704.25	99.90	99.9	0	52.6632153	10.27001776	5.127860196	83.68108984
39	22-Mar-16	7,695.55	7,728.20	7,643.80	7,714.90	10.65	10.65	0	44.26057224	8.216014211	5.387109991	84.34346674
40	23-Mar-16	7,717.45	7,726.85	7,670.60	7,716.50	1.60	1.6	0	35.72845779	6.572811368	5.435795398	84.46190505
41	28-Mar-16	7,741.00	7,749.40	7,587.70	7,615.10	-101.40	0	101.4	28.58276623	25.53824909	1.119214012	52.81269403
42	29-Mar-16	7,606.55	7,652.90	7,582.25	7,597.00	-18.10	0	18.1	22.86621299	24.05059928	0.950754396	48.73778052
43	30-Mar-16	7,651.10	7,741.95	7,643.45	7,735.20	138.20	138.2	0	45.93297039	19.24047942	2.387309037	70.47804056
44	31-Mar-16	7,727.65	7,777.60	7,702.00	7,738.40	3.20	3.2	0	37.38637631	15.39238354	2.428888042	70.8360265
45	1-Apr-16	7,718.05	7,740.15	7,666.10	7,713.05	-25.35	0	25.35	29.90910105	17.38390683	1.72050514	63.24212054
46	4-Apr-16	7,733.15	7,764.45	7,704.40	7,758.80	45.75	45.75	0	33.07728084	13.90712546	2.378441248	70.40055083
47	5-Apr-16	7,736.30	7,736.30	7,588.65	7,603.20	-155.60	0	155.6	26.46182467	42.24570037	0.626379121	38.51372125
48	6-Apr-16	7,636.05	7,638.65	7,591.75	7,614.35	11.15	11.15	0	23.39945974	33.7965603	0.692362167	40.91099297
49	7-Apr-16	7,630.40	7,630.75	7,535.85	7,546.45	-67.90	0	67.9	18.71956779	40.61724824	0.460877302	31.54798158
50	8-Apr-16	7,542.35	7,569.35	7,526.70	7,555.20	8.75	8.75	0	16.72565423	32.49379859	0.514733733	33.98179637
51	11-Apr-16	7,577.80	7,678.80	7,516.85	7,671.40	116.20	116.2	0	36.62052339	25.99503887	1.408750476	58.48469943
52	12-Apr-16	7,669.25	7,717.40	7,663.35	7,708.95	37.55	37.55	0	36.80641871	20.7960311	1.769877076	63.89731484
53	13-Apr-16	7,777.15	7,864.80	7,772.20	7,850.45	141.50	141.5	0	57.74513497	16.63682488	3.470922811	77.63325286
54	18-Apr-16	7,908.15	7,920.60	7,842.75	7,914.70	64.25	64.25	0	59.04610797	13.3094599	4.436401507	81.60547931
55	19-Apr-16	7,950.05	7,950.40	7,877.55	7,914.75	0.05	0.05	0	47.24688638	10.64756792	4.437340689	81.60865656
56	20-Apr-16	7,953.65	7,978.45	7,884.10	7,912.05	-2.70	0	2.7	37.7975091	9.058054338	4.17280662	80.66813485
57	21-Apr-16	7,891.80	7,923.35	7,873.35	7,899.30	-12.75	0	12.75	30.23800728	9.79644347	3.08663112	75.5299666
Dynamic RSI

Cell Formulas
Range	Formula
F5:F57	F5	=E5-E4
G5:G57	G5	=IF(F5>0,F5,0)
H5:H57	H5	=IF(F5<0,-F5,0)
I5,I9:I57	I5	=IF((ROW()-4-$L$3)<0,"",IF((ROW()-4-$L$3)>0,((I4*($L$3-1))+G5)/$L$3,IF((ROW()-4-$L$3)=0,AVERAGE($G$5:INDIRECT("G"&ROW())))))
J5:J57	J5	=IF((ROW()-4-$L$3)<0,"",IF((ROW()-4-$L$3)>0,((J4*($L$3-1))+H5)/$L$3,IF((ROW()-4-$L$3)=0,AVERAGE($H$5:INDIRECT("H"&ROW())))))
K5:K57	K5	=IF((ROW()-4-$L$3)>=0,I5/J5,"")
L5:L57	L5	=IF((ROW()-4-$L$3)>=0,IF(J5=0,100,100-(100/(1+K5))),"")
I6:I8	I6	=IF((ROW()-4-$L$3)<0,"",IF((ROW()-4-$L$3)>0,((I5*($L$3-1))+G6)/$L$3,IF((ROW()-4-$L$3)=0,AVERAGE($G$5:INDIRECT("H"&ROW())))))

 

[Bosquedeguate]

Since you have a working worksheet. Why don't you just hide the columns with data that you do not need displayed?
Having the intermediate values could be useful if you ever have problems.

But, to answer your question ... "Yes" you could simplify the formula and eliminate columns if that is what want to do.

 

[GUYNOUK]

Hi. Thanks for the reply. I did think of hiding the columns but I'll be expanding this sheet and am looking to rationalise .... but i'm struggling to get this into one formula.

 

[Bosquedeguate]

Hello:
I have been looking at your data and formula. Here is what I have concluded ...
Column K could be eliminated by doing the Column I/Column J division in your RSL column

Columns I and J are much harder to eliminate. That is because the formula changes.
These two columns appear to be a modified EMA calculation (Exponential Modified Average). It quite a bit of digging and looking at stock performance calculation to figure this out.

As the calculation title implies it is a smoothing formula and is dependent on previous calculations. The first values in columns I and J are based upon a simply Average calculation. In your sample data
the Average(Column G) and Average(Column H) are the values for Columns I and J for row 9 when the number of sample is set to 5 (in your sample data).

However, the calculation changes when the row number is 10 or greater to form of an EMA calculation (which is cumulative). It appears to give an exponential weight to previous values of Gains (Column G) and Losses (Column H). But, it is skewed by the initial average calculation in Row 9.

What I would suggest if you do not want to hide columns is put the calcuations for Columns I, J and K on a different sheet and reference their values in your formulas.

Would that be a workable solution for you?