help to resolve IF statement that calculates 2 sums to zero with certain variables in calc

Originally Posted by DarthMoose

I'm a little confused as to what the issue is that you are running into ...

I retyped your formula the way that you have it above and it works great. What issue are you encountering?

Okay... I add scheduled hours per day on a schedule, if the sum hits the number 29 or 40 the formula breaks down.
schedule.JPG The first row remains totally functionable. The second row begins the problem

Originally Posted by daaby

I am using the following [=IF(AA12=1,IF(X12<>0,X12-40,X12),IF(AA12=0.5,IF(X12<>0,X12-29,X12)))] calculation for a plus or minus calculation on a schedule which is based on 29 or 40 hours per week.
The problem is when scheduled hours = 29 or 40 hours I get a funky result calculation in the cell =IF(AA12>=1,IF(X12<>0,X12-40,X12),IF(AA12=0.5,IF(X12< -7.10543E-15>0,X12-29,X12)))
I have not been able to figure this out
Anyone have any solutions???

The 1 = a true statement if full time and the .5 is if it is a part time 29 hours from a check box another sheet.

The original cell just above calculates perfectly. the cells below give a funky cell result.

I'm currently wondering if you made a typo in your OP....-7.10543E-15 ...not the number itself, but rather the placement within your post, as the formula with it included makes no sense...

Taking it out of the formula, I get...
.
=IF(AA12>=1,IF(X12<>0,X12-40,X12),IF(AA12=0.5,IF(X12<>0,X12-29,X12)))

...which makes more sense.

Well it helps verify what you are seeing... but I cannot determine why. FWIW, setting the cells to Number format "masks" the problem.

Well I have been through the code a great many times including formatting. I am pretty much a novice at this. I started excel a little over a month ago, but it is similar to php so I caught on fairly well, but not great; yet...Again any insight will be helpful... But I really need the plus or minus hours from 29 and 40 hours, and the 0 hours is also important.
Now the checkbox when checked gives full time 40 hrs and unchecked gives 29 hours.
The only odd thing is that the flaw does not occur in the first row. I have been stuck a week on this and it is all I need to complete this portion of my project.

Try this formula in X6 and copy down...

=X6-40+IF(AA6=0.5,11,0)

(Note formula doesn't include any handling of situations where AA6 may be other than 1 or 0.5)

Originally Posted by vlady

instead of =sum(AC6+AE6+AG6+AI6+AK6+AM6+AO6) in column X6 down
use below. It's precision problem.

=ROUND(AC6+AE6+AG6+AI6+AK6+AM6+AO6,0)


edit:
and btw regarding the formula llike this one..=IF(ISBLANK(B6),"0",SUM((D6-C6+(D6<C6))*24)) do not put quotes just 0

Regards,
Vladimir

Actually I need the total hours scheduled in CX6 and this formula gives the cell a sum of zero. The example does not work as it should. drop downs do not work and neither do the check boxes for full or part time

Here is a live copy that funtions...

As noted by vlady, its a precision problem. It's a result of Excel tracking time values as a decimal value. As such, there will always be rounding errors compounded when performing operations on more than one time value... because each time value is rounded to decimal form. 7 days a week, two time values per day, means the compounded rounding error could be anywhere between 0 and 14 times, inclusive.

vlady gave you an easy way out as far as modifying your formulas.

I understand now what Vlady was saying. Thank you for clarification. Is there anyway around this???
=ROUND(AC6+AE6+AG6+AI6+AK6+AM6+AO6,0) gives me zero in the cell where I would like to track the total hours. I guess I would have to have two columns one for hours and the other for plus or minus hours... Unless there are any ideas.
I appreciate everyone's help and education in this matter. I am very novice yet...

Originally Posted by daaby

I understand now what Vlady was saying. Thank you for clarification. Is there anyway around this???
=ROUND(AC6+AE6+AG6+AI6+AK6+AM6+AO6,0) gives me zero in the cell where I would like to track the total hours. I guess I would have to have two columns one for hours and the other for plus or minus hours... Unless there are any ideas.
I appreciate everyone's help and education in this matter. I am very novice yet...

You already have two columns as such X and Z, respectively.

BTW, I notice you time assignments are in 15 minute intervals. You should change the round formula to...

=ROUND(AC6+AE6+AG6+AI6+AK6+AM6+AO6,0.00)