Help to Reduce Formula

drhacker

New member
Joined
Mar 11, 2014
Messages
1
Reaction score
0
Points
0
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.
 
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.
 
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
 
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.
 
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.
 
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
 
i mean the equal in the second formula; IF(COUNTIF(E10:E18,Complete)=COUNTIF(E10:E18,
 
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.
 
Surely, it is testing whether that range only contains the value Complete?
 
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".
 
Back
Top