PDA

View Full Version : Solved: Combine Formula



jammer6_9
03-28-2012, 11:37 AM
I was having troubloe combining formula to read 2 separate criteria. :help

Bob Phillips
03-28-2012, 03:25 PM
Use

=--(OR(AND(J$7>=$D8,($D8+$E8*(1/24))>J$7),AND(J$7>=$G8,($G8+$H8*(1/24))>W$7)))

but ...

Why are you using a formula to determine whether the condition is true and transforming that to 1 or 0, then using CF on a value of 1, why not just use

=OR(AND(J$7>=$D8,($D8+$E8*(1/24))>J$7),AND(J$7>=$G8,($G8+$H8*(1/24))>W$7))

as your CF formula?

jammer6_9
03-28-2012, 11:30 PM
Actually I have spent a lots of time twisting the formula and I have ended up to that until I finally decided to call for a help :( ... Your suggestion works perfectly by modifying a little bit as you gave the idea.

=OR(AND(I$7>=$D8,($D8+$E8*(1/24))>I$7),AND(I$7>=$G8,($G8+$H8*(1/24))>I$7))

But still seems to have a problem as attached. Defined work hours was 4 but showing on the shaded as 5 hours.




Use

=--(OR(AND(J$7>=$D8,($D8+$E8*(1/24))>J$7),AND(J$7>=$G8,($G8+$H8*(1/24))>W$7)))

but ...

Why are you using a formula to determine whether the condition is true and transforming that to 1 or 0, then using CF on a value of 1, why not just use

=OR(AND(J$7>=$D8,($D8+$E8*(1/24))>J$7),AND(J$7>=$G8,($G8+$H8*(1/24))>W$7))

as your CF formula?

Bob Phillips
03-29-2012, 01:28 AM
I think your problem is in the floating point processor, dividing the number of hours by 24 is just not accurate in all cases (not to mention that odd practice of multiply by 1 and divide by 24, *1/24, this is wasteful, /24 is the same). Anyway, rather than divide the hours by 24, changed it to multiply the times by 24 which means that it is all integer arithmetic.

=OR(AND(I$7>=$D8,($D8*24+$E8)>I$7*24),AND(I$7>=$G8,($G8*24+$H8)>I$7*24))

You seem to have missed my point of CF formula. I meant put the formula in the CF directly, not in the cell at all.

jammer6_9
03-29-2012, 01:44 AM
:banghead: Yeah I missed it... Thanks once again xld :thumb ... Its amazing. I never did that. I never have thought that I can put the formula in the CF not in the cell where it will be like a magic to the user :rotlaugh:




You seem to have missed my point of CF formula. I meant put the formula in the CF directly, not in the cell at all.

Bob Phillips
03-29-2012, 02:10 AM
That's great, your problem is solved and your own skills-base has moved up a notch. Double whammy :)

PatrickM
03-29-2012, 02:44 AM
Hey! I wonder why on my PC nothing works. So after reading this post and the conversations I did all the settings again but nothing works.

jammer6_9
03-29-2012, 03:14 AM
Hi Patrick, instead of putting the formula below directly in the cell
=OR(AND(I$7>=$D8,($D8*24+$E8)>I$7*24),AND(I$7>=$G8,($G8*24+$H8)>I$7*24))

put instead in CF - Conditional Formatting :yes


Hey! I wonder why on my PC nothing works. So after reading this post and the conversations I did all the settings again but nothing works.