LinkBack URL
About LinkBacks
Hi guys,
Any ideas how to make one formula out of the following 2 formulas?
and
Please Login or Register to view this content.
I simply can't find itPlease Login or Register to view this content.
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?
andPlease Login or Register to view this content.
I simply can't find itPlease Login or Register to view this content.
I'm sure is a simple one.
Thanks a lot!
Maybe this:
Formula:
Please Login or Register to view this content.
Never use Merged Cells in Excel
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...
Thanks for your quick reply!Originally Posted by zbor
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
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?
duplicate 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.
Try
Formula:Please Login or Register to view this content.
Looks like you may need to replace , with ; for your locale settings.
Perhaps an addition to zbor's solution
=MIN(7,MAX(-3,NETWORKDAYS(AB2;AE2;Table13)-SIGN(AE2-AB2)))
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:
which deals with the 0 value and other issues.Please Login or Register to view this content.
I need to complete this formula for the [value_if_false] with the mixture between those two.
YES, this is the one!!!
Thank you so much everybody! Your are all so great, as usual!
Indeed, I have different settings.Try
Formula:Please Login or Register to view this content.
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.
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