• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Ifs value returning #N/A

PendekarMakao

PendekarMakao

New Member
Dear all,

I have problems with my ifs formula because its returning #N/A.

Could someone help me to find out the problems?

Thank you,
Regards
 

Attachments

  • Work Hour.zip
    832.5 KB · Views: 7
Seriously???

So you attach a MASSIVE workbook that takes ages to open and then expect us to reverse engineer a hugely long and complex formula with no explanation from you as to what it is meant to be doing? You're having a laugh! I don't think so.

1. Provide a SMALL sample workbook that does not require zipping (copy and paste relevant material into a new workbook and share that).
2. Explain the logic of the formula and the calculation it is meant to be making.
3. Give us some expected results (manually calculated).

EDIT: Still waiting for the workbook to open, so have given up.
 
Ali, FYI, it opened immediately for me but I didn't even start (or try, for that matter) to understand what the formula is supposed to do...:DD
 
Oh I am sorry for the lack of information.

Actually the problem is coming from this formula only

AND(TEXT(L$1;"dddd")="Jumat";VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!O4)-COLUMN(Schedule!$I4))+3;0)="out";OR(AND(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!N4)-COLUMN(Schedule!$H4))+2;0)=5;VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0)=VLOOKUP(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0);'In Out'!$A$32:$B$38;2;0));AND(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!N4)-COLUMN(Schedule!$H4))+2;0)=7;VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0)=VLOOKUP(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0);'In Out'!$A$18:$B$31;2;0));AND(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!N4)-COLUMN(Schedule!$H4))+2;0)=8;VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0)=VLOOKUP(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0);'In Out'!$A$3:$B$17;2;0))));TEXT(VLOOKUP(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!N4)-COLUMN(Schedule!$H4))+2;0);Friday!$A$2:$B$5;2;0);"h:mm")

I am trying to calculate working hours spent by the employees, exclude overtime work. The working schedule sheet shows entry (column In) and leave time (column Out), sometimes it also includes overtime work carried out by the employees. That is why I also need to check the OT column to see whether they have overtime work at the beginning of their shift (column OT=out) or at the end of their shift (column OT=in).

On Friday, if the employee comes or leaves for work at certain times (indicated on sheet "In Out"), the working hours spent will be added for another 30 minutes (sheet Friday). For example, this formula should return with 08:30 value.

I have put all of the logic that I want to test, but the value returning keep shows #N/A, especially on Friday.

Could you please tell me where the problem is?

Thank you
 
You still haven't given us any EXPECTED RESULTS (manually calculated) in the SMALLER sample workbook that I requested.
 
Hi Ali, as I said. Manually calculated, it should return with 8:30 value. --> sorry my mistake, it should return with 9.30.

Because AND(TEXT(L$1;"dddd")="Jumat" is true.

VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!O4)-COLUMN(Schedule!$I4))+3;0)="out" is also true.

AND(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!N4)-COLUMN(Schedule!$H4))+2;0)=8 is true.

VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0)=VLOOKUP(VLOOKUP($A3;Schedule!$1:84;4*(COLUMN(Schedule!L4)-COLUMN(Schedule!$F4));0);'In Out'!$A$3:$B$17;2;0)))) is also true.

Thank you.
 
Last edited:
Sorry - I still don't have a workbook that I can open, so I'm afraid I can't help.

All I an suggest is that you test each of those bits of the formula in separate columns and check that they are returning what you expect. Break the formula down as much as you can.

Good luck!
 
Thank you, I already found where the problem comes from.

One of my logical test is resulting with #N/A that is why the whole value returning with #N/A.

Thank you for your suggestion.

Regards
 
Well there you go. Next time, you'll know how to troubleshoot a long formula yourself.
 
You must log in or register to reply here.
Back
Top