WizzardOfOz
New member
- Joined
- Sep 4, 2013
- Messages
- 184
- Reaction score
- 0
- Points
- 0
- Location
- Australia
- Excel Version(s)
- Office 365
I want to clean up a complicated array formula.
My if statement is
=IF(ISERROR(2),"", IF(OR(2<0,2=1),"",2))
(i.e. if X <0 or X = 1 or X is an error return nothing else return X (0 to 1) e.g 0.1
where X must get replaced with the array formula
X=CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))
(i.e. do the correlation only where both Y and X is greater than zero)
This looks messy
=IF(ISERROR(CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))),"",
IF(OR(CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))<0,
CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))=1),"",
CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))))
Oh and I'm repeating it 21 x 74 times
I tried a UDF but PC went into SLOW MODE
My if statement is
=IF(ISERROR(2),"", IF(OR(2<0,2=1),"",2))
(i.e. if X <0 or X = 1 or X is an error return nothing else return X (0 to 1) e.g 0.1
where X must get replaced with the array formula
X=CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))
(i.e. do the correlation only where both Y and X is greater than zero)
This looks messy
=IF(ISERROR(CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))),"",
IF(OR(CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))<0,
CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))=1),"",
CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))))
Oh and I'm repeating it 21 x 74 times
I tried a UDF but PC went into SLOW MODE
Last edited: