# 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!

2. ## Re: Mixing two formulas.

Maybe this:

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

3. ## Re: Mixing two formulas.

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

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.

5. ## Re: Mixing two formulas.

duplicate post

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.

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?

8. ## Re: Mixing two formulas.

duplicate post

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.

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
?

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.

12. ## Re: Mixing two formulas.

Perhaps an addition to zbor's solution

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

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.

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!

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.

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.

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.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1