Formula is too long

[Guest]

Hi,

I am making an attendance sheet, where I have to give leaves if an employee
is absent and he/she has leave balance in his/her account. Presently I am
doing it manually but I am trying to make it automatically. I was making a
formula, but due to many condition the formula become long and still I have
to add 4-5 condition.

Formula is below mentioned.

IF(WEEKDAY(($E$2+N$5),2)=1&7,"",IF(AND(OR(IF(WEEKDAY(($IH$23+BX$5),2)=1,COUNTIF(BX7:CC7,"A")=5,COUNTIF(BX7:CC7,"A")=4),IF(WEEKDAY(($IH$23+BY$5),2)=1,COUNTIF(BY7:CD7,"A")=5,COUNTIF(BY7:CD7,"A")=4),IF(WEEKDAY(($IH$23+BZ$5),2)=1,COUNTIF(BZ7:CE7,"A")=5,COUNTIF(BZ7:CE7,"A")=4),IF(WEEKDAY(($IH$23+CA$5),2)=1,COUNTIF(CA7:CF7,"A")=5,COUNTIF(CA7:CF7,"A")=4),IF(WEEKDAY(($IH$23+CB$5),2)=1,COUNTIF(CB7:CG7,"A")=5,COUNTIF(CB7:CG7,"A")=4),IF(WEEKDAY(($E$2+CC$5),2)=1,COUNTIF(CC7:CH7,"A")=5,COUNTIF(CC7:CH7,"A")=4)),IF(COUNTIF(N7:R7,"PL")>=3,$HY7>=1,IF(COUNTIF(N7:R7,"PL")=2,$HY7>=2,IF(COUNTIF(N7:R7,"PL")=1,$HY7>=3,$HY7>=3)))),"PL",""))&IF(AND(NOT(R7="PL"),NOT(R7="SL"),NOT(R7="SL/2"),NOT(R7="SL/4"),CC7="A",$HW7>=1),"CL",IF(AND(NOT(R7="PL"),NOT(R7="CL"),NOT(R7="CL/2"),NOT(R7="CL/4"),CC7="A",$HX7>=1),"SL",IF(AND(NOT(R7="PL"),NOT(R7="SL/2"),NOT(R7="SL"),NOT(R7="SL/4"),CC7="A/2",$HW7>=0.5),"CL/2",IF(AND(NOT(R7="PL"),NOT(R7="CL/2"),NOT(R7="CL"),NOT(R7="CL/4"),CC7="A/2",$HX7>=0.5),"SL/2",""))))

 

[Roger Govier]

WoW!!!
You're not kidding!!

This formula will not work for many reasons.
Even if it did, it would be almost completely un-maintainable in the
future.

You need to break it down into a series of tests using helper cells,
then look at the results of the helper cells in combination to determine
your answer.

See if you can explain in simple terms (not by formula)
What data you have in what locations
What it is that you are trying to achieve.

 

[JLatham]

I agree with Roger that if nothing else, maintaining it is a potential
nightmare. I would consider his suggestion of breaking it into pieces in
helper cells and then using a final formula to evaluate the results of the
helper cells to give a final answer. I would also suggest that you add
comments to the helper cells in the first row where they are used to remind
you of what each is doing for future maintenance.

Another option would be to turn the entire process into a User Defined
Function using VBA which will result in better readability, maintainability
and again allow you to use a single cell for the process without the
limitation on formula length.