# Mixing two formulas.

1. ## Mixing two formulas.

Hi guys,

Any ideas how to make one formula out of the following 2 formulas? ``Please Login or Register  to view this content.``
and ``Please Login or Register  to view this content.``
I simply can't find it I'm sure is a simple one.

Thanks a lot!  Register To Reply

2. ## Re: Mixing two formulas.

Maybe this:

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Mixing two formulas.

What do you want the formula to be when (AE2-AB2) is between -3 and 7 ?  Register To Reply

4. ## Re: Mixing two formulas. Originally Posted by zbor Maybe this:

Formula:  `Please Login or Register  to view this content.`

Is not this one. The point is for my first formula to return -1, if I have in AB2 "07.03.2014" and in AE2 "06.03.2014", and not -3 as yours do.  Register To Reply

5. ## Re: Mixing two formulas.

duplicate post  Register To Reply

6. ## Re: Mixing two formulas. Originally Posted by Olly What do you want the formula to be when (AE2-AB2) is between -3 and 7 ?
To return the right value, but not bigger than -3 or 7.  Register To Reply

7. ## Re: Mixing two formulas. Originally Posted by boboivan To return the right value, but not bigger than -3 or 7.
Which is the right value?!

In your first formula, you calculate NETWORKDAYS(AB2;AE2;Table13)+1
In your second formula, you calculate NETWORKDAYS(AB2;AE2;Table13)-1

See the difference? Which one do you consider right?  Register To Reply

8. ## Re: Mixing two formulas.

duplicate post  Register To Reply

9. ## Re: Mixing two formulas. Originally Posted by Olly Which is the right value?!

In your first formula, you calculate NETWORKDAYS(AB2;AE2;Table13)+1
In your second formula, you calculate NETWORKDAYS(AB2;AE2;Table13)-1

See the difference? Which one do you consider right?
Yes, the right value for positive numbers is NETWORKDAYS(AB2;AE2;Table13)-1 and for the negative numbers is NETWORKDAYS(AB2;AE2;Table13)+1.  Register To Reply

10. ## Re: Mixing two formulas. Originally Posted by boboivan Yes, the right value for positive numbers is NETWORKDAYS(AB2;AE2;Table13)-1 and for the negative numbers is NETWORKDAYS(AB2;AE2;Table13)+1.
Your 2 formulas account for values of
LESS THAN -3, it should retrun -3
GREATER THAN 7, it should return 7

But the 2 formulas do different things for values between -3 and 7
What should it be for
-3, -2, -1, 0, 1, 2, 3, 4, 5, 6 and 7
?  Register To Reply

11. ## Re: Mixing two formulas.

Try
Formula:  `Please Login or Register  to view this content.`

Looks like you may need to replace , with ; for your locale settings.  Register To Reply

12. ## Re: Mixing two formulas.

Perhaps an addition to zbor's solution

=MIN(7,MAX(-3,NETWORKDAYS(AB2;AE2;Table13)-SIGN(AE2-AB2)))  Register To Reply

13. ## Re: Mixing two formulas. Originally Posted by Jonmo1 Your 2 formulas account for values of
LESS THAN -3, it should be the networkdays+1
GREATER THAN 7, it should be the networkdays-1

There is no context for what you want if the it's BETWEEN -3 and 7, say 4 for example...should that be -1 or +1, or just 0 ?
So, if is less than -3 (-4,-5,-6,etc) should be -3. If is between -1 and -3, should be -1, -2 or -3.
If is greater than 7 (8,9,10,etc) should be 7. If is between 1 and 7, should be 1,2,3,4,5,6 or 7.

In fact my full formula starts with: ``Please Login or Register  to view this content.``
which deals with the 0 value and other issues.
I need to complete this formula for the [value_if_false] with the mixture between those two.  Register To Reply

14. ## Re: Mixing two formulas. Originally Posted by Jonmo1 Perhaps an addition to zbor's solution

=MIN(7,MAX(-3,NETWORKDAYS(AB2;AE2;Table13)-SIGN(AE2-AB2)))
YES, this is the one!!!

Thank you so much everybody! Your are all so great, as usual!  Register To Reply

15. ## Re: Mixing two formulas. Originally Posted by Olly Try
Formula:  `Please Login or Register  to view this content.`

Looks like you may need to replace , with ; for your locale settings.
Indeed, I have different settings.

This formula returns 7 instead of 5, when I have for example in AB2=07.03.2014 and in AE2=14.03.2014. Don't bother with it anymore, cos' Jonmo1's solution works great.
Thank you so much for your kind support.  Register To Reply

16. ## Re: Mixing two formulas.

Credit to zbor too, I just added function to account for -1 or +1 depending on positive or negative results.  Register To Reply

17. ## Re: Mixing two formulas. Originally Posted by Jonmo1 Credit to zbor too, I just added function to account for -1 or +1 depending on positive or negative results.
Of course. Zbor's solution was a great start.  Register To Reply