# Thread: Help to Reduce Formula

1. ## Help to Reduce Formula

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.  Reply With Quote

2. 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.  Reply With Quote

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  Reply With Quote

4. Originally Posted by toony 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.  Reply With Quote

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.  Reply With Quote

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  Reply With Quote

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

8. Originally Posted by toony 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.  Reply With Quote

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

10. 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".  Reply With Quote

#### Posting Permissions

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