The formula below can produce two results. I need a third that will be a 1 if the first two conditions are not met.
Thanks
=IF(OR((M17+R17)=N17,(M17+R17)<N17),0,-1)
That formula should be simplified as
=IF(M17+R17<=N17,0,-1)
and you are already saying you want -1 if the conditions are not met. What condition(s) would require a 1 as the result?
Hi rwhalls and welcome to the forum. How about
=IF(AND((M17+R17)<>N17,(M17+R17)>=N17),1,IF(OR((M17+R17)=N17,(M17+R17)<N17),0,-1))
One test is worth a thousand opinions.
Click the * below to say thanks.
This will give a 1 if the sum of M17 and R17 is greater than N17.=if((m17+r17)>n17,1,if((m17+r17)<=n17,0,-1))
This will give a 0 if the sum of M17 and R17 is equal to or less than N17.
This will give a -1 if there is an error in the formula (word in the cell instead of a number.)
Last edited by eg0e; 03-08-2011 at 05:09 PM.
Thanks. I had to take out the 0 before the -1 to make it work
I put back the 0 before the – 1 but there is a problem. When M17+R17 is less than N17 the result is 0 and it should be -1
I think you should be showing us what formula you are using!!
Perhaps then you actually want:Originally Posted by rwhalls
=SIGN(M17+R17-N17)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
This formula does not produce a -1
=IF(AND((M17+R17)<>N17,(M17+R17)>=N17),1,IF(OR((M17+R17)=N17,(M17+R17)<N17),0,-1))
For the time being try to explain requirements in words rather than failed formulas which in truth are hard to follow (logically)
The SIGN formula will generate -1, 0, 1 depending on result of M17+R17 less N17 with results as follows:
If M17+R17 is less than N17 then -1
If M17+R17 is equal to N17 then 0
If M17+R17 is greater than N17 then 1
If that's not what you require please clarify with actual examples / values.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Here's the formula:
=IF(AND((M17+R17)<>N17,(M17+R17)>=N17),1,IF(OR((M17+R17)=N17,(M17+R17)<N17),0,-1))
If you feel the formula above works that's great obviously but from a logic perspective it makes little sense given -1 would never be generated
either M17+R17 >N17 (1) or it is <= N17 (0) ... on which basis both the AND and the OR tests are unnecessary - single tests of > and <= would have the same effect
If you need to follow up please explain your requirements in words
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
N17 can be equal to, less than or greater than M17+R17. If equal to a 0 is entered. If less than a -1 is entered. If greater than a 1 is entered.
This is what the SIGN formula in post # 8 does only in reverse given we assumed the -1 occurred where M+R < N rather than the other way around
will generate -1, 0 or 1 as you outline.=SIGN(N17-M17-R17)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks