Need help with formula

[DKRbella0814]

Can someone help me identify the error in this formula; I may have missed a parenthesis someplace. Thanks in advance for your help.

=IF(AND(ISBLANK(VLOOKUP(A2,Data!B2:DU2,13,FALSE)),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)<>"")),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)/60),IF(AND((VLOOKUP(A2,Data!B2:DU2,13,FALSE)<>""),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)<>"")),((VLOOKUP(A2,Data!B2:DU2,13,FALSE)*60)+VLOOKUP(A2,Data!B2:DU2,14,FALSE))/60),if(and((isblank(VLOOKUP(A2,Data!B2:DU2,13,FALSE))),(isblank(VLOOKUP(A2,Data!B2:DU2,14,FALSE)))),"",(VLOOKUP(A2,Data!B2:DU2,13,FALSE))

 

[Aladin Akyurek]

Try to word what that formula is supposed to do...

 

[MrKowz]

I don't think your entire code posted (it abruptly ends). If you have any > or < in the formula, put a space before and after.

Also, please explain what you want the formula to do. Trying to debug a formula that incredibly wordy without knowing the purpose is almost impossible, as there could be more underlying problems.

 

[barry houdini]

Does this do it?

=IF(AND(ISBLANK(VLOOKUP(A2,Data!B2:DU2,13,FALSE)),VLOOKUP(A2,Data!B2:DU2,14,FALSE)<>""),VLOOKUP(A2,Data!B2:DU2,14,FALSE)/60,IF(AND(VLOOKUP(A2,Data!B2:DU2,13,FALSE)<>"",VLOOKUP(A2,Data!B2:DU2,14,FALSE)<>""),(VLOOKUP(A2,Data!B2:DU2,13,FALSE)*60+VLOOKUP(A2,Data!B2:DU2,14,FALSE))/60,IF(AND(ISBLANK(VLOOKUP(A2,Data!B2:DU2,13,FALSE)),ISBLANK(VLOOKUP(A2,Data!B2:DU2,14,FALSE))),"",VLOOKUP(A2,Data!B2:DU2,13,FALSE))))

 

[DKRbella0814]

If the s/u time (min) is blank AND the s/u time (hrs) is not blank, then return the s/u time in hrs and divide by 60 - to convert to hrs

If the s/u time (min) is not blank and s/u time in (hrs) is not blank, then (return s/u time (min) *60 + s/u time in hrs)/60 to convert to hrs

If the s/u time (min) AND s/u time (hrs) are blank then ""

Otherwise, return s/u time (hrs)

*The formula was working up until the last if( statement
I'm guessing that it is a parenthesis problem. Please help me figure this out!


=IF(AND(ISBLANK(VLOOKUP(A2,Data!B2:DU2,13,FALSE)),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)<>"")),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)/60),IF(AND((VLOOKUP(A2,Data!B2:DU2,13,FALSE)<>""),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)<>"")),((VLOOKUP(A2,Data!B2:DU2,13,FALSE)*60)+VLOOKUP(A2,Data!B2:DU2,14,FALSE))/60),if(and((isblank(VLOOKUP(A2,Data!B2:DU2,13,FALSE))),(isblank(VLOOKUP(A2,Data!B2:DU2,14,FALSE)))),"",(VLOOKUP(A2,Data!B2:DU2,13,FALSE))

 

[DKRbella0814]

If the s/u time (min) is blank AND the s/u time (hrs) is not blank, then return the s/u time in hrs and divide by 60 - to convert to hrs

If the s/u time (min) is not blank and s/u time in (hrs) is not blank, then (return s/u time (min) *60 + s/u time in hrs)/60 to convert to hrs

If the s/u time (min) AND s/u time (hrs) are blank then ""

Otherwise, return s/u time (hrs)

*The formula was working up until the last if( statement
I'm guessing that it is a parenthesis problem. Please help me figure this out!


=IF(AND(ISBLANK(VLOOKUP(A2,Data!B2:DU2,13,FALSE)),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)<>"")),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)/60),IF(AND((VLOOKUP(A2,Data!B2:DU2,13,FALSE)<>""),(VLOOKUP(A2,Data!B2:DU2,14,FALSE)<>"")),((VLOOKUP(A2,Data!B2:DU2,13,FALSE)*60)+VLOOKUP(A2,Data!B2:DU2,14,FALSE))/60),if(and((isblank(VLOOKUP(A2,Data!B2:DU2,13,FALSE))),(isblank(VLOOKUP(A2,Data!B2:DU2,14,FALSE)))),"",(VLOOKUP(A2,Data!B2:DU2,13,FALSE))

 

[DKRbella0814]

Barry, your solution worked. Thanks for the help.

 

[DKRbella0814]

HOw would I add this to the formula?

If after all logic is followed, the result is an error, return ""

 

[barry houdini]

Which version of Excel are you using?

 

[DKRbella0814]

excel 2003