Having trouble with my nested formula returning a value error

It's going to be difficult to help you if we don't have the data. I am trying to reverse engineer what you are trying to do and it's going to be hard if I have no idea what is in O2, why you care if it is "yes", and also what's in AF2 and why you care if it's 1.

I am guessing that you just have a syntax error in your nesting. Here is my best guess at fixing your syntax, and it works when I make up some fake data:

=IF(AND(O2="yes",AF2=1,ROUND(MOD(L2-K2,1)*24,2)>12),ROUND(MOD(L2-K2,1)*24,2)-1,IF(AND(O2="yes",AF2=1,ROUND(MOD(L2-K2,1)*24,2)<12),ROUND(MOD(L2-K2,1)*24,2)-0.5,ROUND(MOD(L2-K2,1)*24,2)))

Guess this is calculating how long a break to deduct from paid hours. Couldn't work out where it was going wrong so I started from scratch:

Formula:

=LET(oaf,AND(O2="yes",AF2=1),hrs,ROUND(MOD(L2-K2,1)*24,2),IF(AND(oaf,hrs>12),hrs-1,IF(AND(oaf,hrs<12),hrs-0.5,hrs)))

Josephteh and 6SJ seem to have corrected the problem with the original version.

TMS solution is overall better because it factors out common logic.

You're welcome.


Note that post #4 removes all the repetitive logic.



Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks for the rep.