LinkBack URL
About LinkBacks
Hi guys,
Any ideas how to make one formula out of the following 2 formulas?
and
=IF((AE2-AB2)<-3;-3;NETWORKDAYS(AB2;AE2;Table13)+1)
I simply can't find it=IF((AE2-AB2)>7;7;NETWORKDAYS(AB2;AE2;Table13)-1)
I'm sure is a simple one.
Thanks a lot!
Hi guys,
Any ideas how to make one formula out of the following 2 formulas?
and=IF((AE2-AB2)<-3;-3;NETWORKDAYS(AB2;AE2;Table13)+1)
I simply can't find it=IF((AE2-AB2)>7;7;NETWORKDAYS(AB2;AE2;Table13)-1)
I'm sure is a simple one.
Thanks a lot!
Maybe this:
Formula:
=MIN(7,MAX(-3,NETWORKDAYS(AB2;AE2;Table13)-1))
Never use Merged Cells in Excel
Thanks for your quick reply!Originally Posted by zbor
Maybe this:
Formula:=MIN(7,MAX(-3,NETWORKDAYS(AB2;AE2;Table13)-1))
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.
duplicate post
Last edited by boboivan; 03-05-2014 at 11:31 AM. Reason: duplicate post
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...
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?
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.
duplicate post
Try
Formula:=IF((AE2-AB2)<-3,-3,IF((AE2-AB2)<0,NETWORKDAYS(AB2,AE2,Table13)+1,IF((AE2-AB2)<7,NETWORKDAYS(AB2,AE2,Table13)-1,7)))
Looks like you may need to replace , with ; for your locale settings.
Indeed, I have different settings.Try
Formula:=IF((AE2-AB2)<-3,-3,IF((AE2-AB2)<0,NETWORKDAYS(AB2,AE2,Table13)+1,IF((AE2-AB2)<7,NETWORKDAYS(AB2,AE2,Table13)-1,7)))
Looks like you may need to replace , with ; for your locale 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.
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!
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks