Results 1 to 10 of 10

Thread: Clean up complex IF statement

  1. #1
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0

    Clean up complex IF statement



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    sorry typo in third line 2 should be X

  3. #3
    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)))

  4. #4
    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)),"")

  5. #5
    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.

  6. #6
    Oh dear..last post..sorry, I shouldn't do this tired

    Actually, re-reading your post, you stated
    (i.e. do the correlation only where both Y and X is greater than zero)
    Your 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.
    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.

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    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),""))


  8. #8
    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.

  9. #9
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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.

  10. #10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •