Results 1 to 10 of 10

Thread: Help to Reduce Formula

  1. #1

    Question Help to Reduce Formula



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

    Hello Guru's.

    Can anyone suggest me how can i reduce / optimize this two formulas ?

    First Formula:

    =IF(R$8=$E$6,"End",IF(R$8=$E$5,"Start",IF(R$8=TODAY(),"Today",IF($J9=R$8,$Q9+1,IF($L9=R$8,$Q9+2,IF(AND(R$8>=$J9,R$8<=$L9),$Q9,""))))))

    Second Formula:

    =IF(COUNTIF(E10:E18,"Late"),"Late",IF(COUNTIF(E10:E18,"At Risk"),"At Risk",IF(COUNTIF(E10:E18,"On Schedule"),"On Schedule",IF(COUNTIF(E10:E18,"Programmed"),"Programmed",IF(COUNTIF(E10:E18,Complete)=COUNTIF(E10:E18,"<>"),"","Complete")))))

    Thanks a lot guys, i really appreciate your help and support.

  2. #2
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Hi there,

    Not really. Probably not what you wanted to hear lol. Unless you setup a separate range to account for your conditions, they still need to be accounted for. You're just accounting for them in a specific order. For example, if you had a range next to E10:E18 that checked each condition, then look at that range, you could probably shorten it up. These aren't huge formulas though.

    Also, I'm assuming the [second] condition of the second to last COUNTIF() should be in quotes. Is that right? It says 'Complete' but it's not in quotes. Unless that's referring to a named range I don't know about.
    Regards,
    Zack Barresse

  3. #3
    That's quit a long formula. Actually, I have an small query. COUNTIF(E10:E18"<>"), does it mean "if blank cells"?. Excuse me for querying other than helping with your query

  4. #4
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Quote Originally Posted by toony View Post
    That's quit a long formula. Actually, I have an small query. COUNTIF(E10:E18"<>"), does it mean "if blank cells"?. Excuse me for querying other than helping with your query
    Hi there,

    It says to count if not blank.

    Edit: also, there is a comma missing in the formula after the passed range address.
    Regards,
    Zack Barresse

  5. #5
    That second formula is very odd isn't it? If it contains Late in that range, it returns Late, even if all the rest are At Risk, On Schedule, Programmed or so on. Seems a bit arbitrary.

  6. #6
    yea, actually I had a similar long one involving late condition too. a wind up formula. how about the second equal sign? I came across equal sign within a formula (2 equals in one formula) but don't know the meaning

  7. #7
    i mean the equal in the second formula; IF(COUNTIF(E10:E18,Complete)=COUNTIF(E10:E18,

  8. #8
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    Quote Originally Posted by toony View Post
    i mean the equal in the second formula; IF(COUNTIF(E10:E18,Complete)=COUNTIF(E10:E18,
    It's just doing a comparison. So basically does formula1 equal formula2. This will return a boolean result (true or false), which is why it's nested in the test parameter of the IF() statement.
    Regards,
    Zack Barresse

  9. #9
    Surely, it is testing whether that range only contains the value Complete?

  10. #10
    Super Moderator Zack Barresse's Avatar
    Join Date
    Mar 2011
    Location
    Oregon, United States
    Posts
    106
    Articles
    0
    COUNTIF(E10:E18,Complete)=COUNTIF(E10:E18,"<>")
    I assume it should have quotes...
    COUNTIF(E10:E18,"Complete")=COUNTIF(E10:E18,"<>")
    In which case the logic here would be all cells which have values must equal "Complete".
    Regards,
    Zack Barresse

Posting Permissions

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