When 2 values are the same, give me zero value

  • Thread starter Thread starter sonar
  • Start date Start date
S

sonar

Hi

when the amount in CB17 is equal to the value in my CC17, my formula i
CC17 freaks out, it gives me the ### error.

CC17 formula:

=IF((CD17)<>"",0,(IF(CB17>
(IF(OR(BW17=0,BX17=0),0,(IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17
TIME(19,0,0),0)))),0,(IF(OR(BW17=0,BX17=0),0,IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17-TIME(19,0,0),0)))-CB17)))

What the formula is supposed to do is:
If my CD17 has no content, return a zero, else (if my CB17 are greate
or equal to (inbedded formula), then return a zero, else (inbedde
formula) less CB17)


How can I change the > sign

=IF((CD17)<>"",0,(IF(CB17>
(IF(OR(BW17=0,BX17=0),0,(IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17
TIME(19,0,0),0)))),0,(IF(OR(BW17=0,BX17=0),0,IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17-TIME(19,0,0),0)))-CB17)))

to an > and equal to CB17 to give me " " or zero (0) without freakin
out?

I hope I have explained it right.

:confused
 
Sonar
I did several testing on your formula and I don't
understand were is it that you get the error, how ever, if
you only want to change the condition from Greater than to
Greather Than or Equal To then just change

.......CB17>(IF(.... TO
.......CB17>=(IF(....

Also you state that and i quote:
...."If my CD17 has no content, return a zero"...
in which case you should change this part

=IF((CD17)<>"",0,(IF(CB17>..... to
=IF((CD17)="",0,(IF(CB17>.....

If your getting ### make sure your column is wide enough...

Cheers
Juan
 
The ####### are due to that formula calculating a negative
time.

I tried helping a week or so ago but I just could not
understand the logic. I have a copy of his ws but I still
can't figure out what he's trying to do. I posted
questions that weren't answered.

Sonar, please post a *very* detailed description. Time
sheets are my specialty.

Biff
 
Hi Biff

Thank you for assisting,

My idea of the following works,

=IF((CD17)<>"",0,(IF(CB17
(IF(OR(BW17=0,BX17=0),0,(IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17
TIME(19,0,0),0)))),0,(IF(OR(BW17=0,BX17=0),0,IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17-TIME(19,0,0),0)))-CB17)))

And as I have explained, it Will first check if there is any text i
CD17, if not it will look to the rest of the formula to see what to do
if there is text, it will return a zero.

If no text it moved to the next part of the formula, that is
If my CB17 is greater than the if statement that follows, then it wil
deduct that formula (which is my night time formula) from the formul
generated in my CB17, if its not greater, it will calculate my nigh
hours less the figure produced in the CB17.

My problem is that when I get the condition where my CB17 figure is
to the fugure that my night time hours produced, it gives me
negative.

If a person works 3hrs overtime, and that 3hours falls after or befor
7am, the formula must look at the overtime (CB17) and see that it
either greater, equal or less than the night formula. which is

(IF(OR(BW17=0,BX17=0),0,(IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17
TIME(19,0,0),0))))

It works when I say if CB17 > than that formula, it must return a
(zero), because the person can not be paid overtime and night time fo
the same hours, If the hours is worked out to be his overtime and th
formula sees that it forms part of his night hours, but if the formul
sees that the overtime hours (CB17) is less than the formula (nigh
hours), then it deducts the overtime from the formula (night hours) t
give the diference as night hours.

If you can take your mind into the frame of thinking in therms o
salaries, wages and rates of pay and that a person can not be pai
night rate and overtime for the same hours, you will understand i
better.

The problem the formula is giving is when the overtime hours is = t
the formula (night hours), then it freeks out somehow, and I don
understand why.

would the suggested >= work in this case?

Regardsa
Sona
 
Try this:

=IF(OR(CD17<>"",BW17=0,BX17=0),0,IF(BW17<7/24,7/24-BW17,0)
+IF(BX17-19/24-CB17>0,BX17-19/24-CB17,0))

These expressions:

7/24 and 19/24

are the same as your use of the TIME() functions:

TIME(7,0,0) and TIME(19,0,0)

Biff
 
Back
Top