+ Reply to Thread
Results 1 to 17 of 17

Mixing two formulas.

  1. #1
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    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. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Mixing two formulas.

    Maybe this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Mixing two formulas.

    What do you want the formula to be when (AE2-AB2) is between -3 and 7 ?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Mixing two formulas.

    Quote Originally Posted by zbor View Post
    Maybe this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks for your quick reply!

    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.
    Last edited by boboivan; 03-05-2014 at 11:29 AM.

  5. #5
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Mixing two formulas.

    duplicate post
    Last edited by boboivan; 03-05-2014 at 11:31 AM. Reason: duplicate post

  6. #6
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Mixing two formulas.

    Quote Originally Posted by Olly View Post
    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. #7
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Mixing two formulas.

    Quote Originally Posted by boboivan View Post
    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. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Mixing two formulas.

    duplicate post

  9. #9
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Mixing two formulas.

    Quote Originally Posted by Olly View Post
    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. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Mixing two formulas.

    Quote Originally Posted by boboivan View Post
    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
    ?
    Last edited by Jonmo1; 03-05-2014 at 11:36 AM.

  11. #11
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Mixing two formulas.

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


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

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Mixing two formulas.

    Perhaps an addition to zbor's solution

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

  13. #13
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Mixing two formulas.

    Quote Originally Posted by Jonmo1 View Post
    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. #14
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Mixing two formulas.

    Quote Originally Posted by Jonmo1 View Post
    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. #15
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Mixing two formulas.

    Quote Originally Posted by Olly View Post
    Try
    Formula: copy to clipboard
    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.
    Last edited by boboivan; 03-05-2014 at 12:10 PM.

  16. #16
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Mixing two formulas.

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

  17. #17
    Forum Contributor
    Join Date
    02-12-2013
    Location
    Brussel
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: Mixing two formulas.

    Quote Originally Posted by Jonmo1 View Post
    Glad to help.
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Mixing Formulas
    By nikkie456 in forum Excel General
    Replies: 1
    Last Post: 09-12-2013, 10:54 AM
  2. Mixing Excel Formulas/Functions with VBA
    By garithmar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-23-2013, 03:26 PM
  3. [SOLVED] Mixing foursomes
    By DOGDAZE in forum Excel General
    Replies: 2
    Last Post: 04-24-2012, 01:08 AM
  4. Mixing Formulas with Validation
    By dandavis1 in forum Excel General
    Replies: 1
    Last Post: 05-05-2009, 05:55 PM
  5. Mixing up the arguments
    By michaelr586 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2005, 05:05 AM

Bookmarks

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