sorry typo in third line 2 should be X
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
Last edited by WizzardOfOz; 2014-11-14 at 06:04 AM.
sorry typo in third line 2 should be X
Hi Wizard, this is my first post so I hope I get it right
This doesn't save much but I think it looks a little prettier (Just my opinion).
=IF(OR(ISERROR(X),X<0,X=1),"",X)
This does save one instance of X but it only allows numbers between 0 and 1. Doesn't allow for 0 itself.
=IF(OR(ISERROR(X),ABS(X-0.5)<0.5),"",X)
Within your ISERROR section, do you need the IF statement or would an error only occur after the correlate?
And if either/both the E30:E76 and INDEX references weren't greater than 0, could the CORREL return a value that would fall in the 0-1 range?
If those two questions were both a NO and you don't need to show 0 then try this:
Code:=IF(OR(ISERROR(CORREL((E30:E76),INDEX(oData,,$A1))),ABS((CORREL((E30:E76),INDEX(oData,,$A1)))-0.5)<0.5),"",CORREL((E30:E76),INDEX(oData,,$A1)))
Also, if you do need to have the IF statement to make sure the numbers are over 0 and you don't have to correlate one number against a null (if the other one was 0 or less) then you could save an IF this way:
IF(AND(E30:E76>0,INDEX(oData,,$A1)>0),CORREL((E30:E76),INDEX(oData,,$A1)),"")
Sorry, I just read what correlate actually does (should've done that before I posted).
I think all my ideas were useless, except maybe removing the IF statement from the ISERROR section.
Oh dear..last post..sorry, I shouldn't do this tired
Actually, re-reading your post, you statedYour formula would replace one range with a null if it was 0 or less. If it was only the one range that was this way then your formula would be trying to correlate one range against a null and therefore provide an error.(i.e. do the correlation only where both Y and X is greater than zero)
If thats the only way an error would happen then I think my last bit of formula would stop any erros and you might be able to lose the whole ISERROR section.
Last edited by Beamer; 2014-11-14 at 06:25 PM.
How about putting the X (array) formula in a separate cell on it's own and then reference that in the formula.
=IFERROR(IF(OR(X1<0,X1=1),"",2),"")
where X1 contains the array formula: =CORREL(IF(E30:E76>0,E30:E76,""),IF(INDEX(oData,,$A1)>0,INDEX(oData,,$A1),""))
Right..final last post with a slight rewrite.
=IF(OR(E30:E76<=0,INDEX(oData,,$A1)<=0,ABS((CORREL((E30:E76),INDEX(oData,,$A1)))-0.5)<0.5),"",CORREL((E30:E76),INDEX(oData,,$A1)))
So it basically says, IF E30:E76 is less then or equal to 0, or INDEX... is less then or equal to 0, or the correlation is not BETWEEN 0 and 1 then post a null ("")
Else if both ranges are greater then 0 and the correlation is between 0 and 1 then post that correlation result.
Thanks Beamer for the comments.
The iserror has to come first otherwise putting them all in one OR(iserror(x), X<0, X=1) still calculates the X<0 and X=1 and returns an error
For this example it has to be a positive correlation (hence ignore negatives) and a perfect correlation (=1) implies insufficient points so also rejected.
Also the data is bad so the correlation function must exclude 0 as a valid point (E30:E76>0)
NBVC, Yep that was what I eventually did whilst waiting to see if any ideas were forthcoming. 21 x 74 cells is small processing compared to the overhead of calculating X four times. I was just hoping that Excel had an easier way, sort of like a virtual temp table. Was very surprised about the overhead of creating an user defined function, presume this is related to the volatile nature of the calculations.
Thanks anyway.
I'm glad you got it going Wizard.
I thought of suggesting what NBVC said, but as I saw $A1 in the formula (locking that reference to column A), I thought your formula needed to be dynamic (changes row and column numbers as you copied down and across).
I was worried that you might need 0 as a result (which my formula wouldn't allow since it only supplied numbers BETWEEN 0 and 1).
You might want to check your formula where it says X<0. You might want that to be X<=0.
Just as thought.
Bookmarks