Multiple IF Statements With One Not Working
Hey
I have the following formula that not all elements are not working
Here is the formula: =IF(J2=R2,"No Movement",IF(R2<J2,"Widening",IF(J2<R2, "Closing",IF(R2>0, "Closed"))))
So the No Movement, Widening, Closing all work. I'm trying to get the Closed to work now, File attached
Thanks in advance
That's because it doesn't work logically. What if R2=J2 and both are bigger than 0? And so on. In what way is that final requirement meant to work?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I've just added the "Closed" criteria. I only want it to say "Closed" when the number is larger than Zero
Most of the numbers are negative numbers
But it won't work unless the number fails to meet all of the criteria that come before it - do you see? That's the problem.
Which cell in your sample workbook do you think should return "Closed"? You haven't said.
The ones where Column R are above Zero and Green
Hi
It would help if the meaning of the columns was disclosed to understand what you are trying to achieve with "Closed"
The following solves the equation though
Cheers
Please Login or Register to view this content.
Last edited by FoxSeaLady; 11-04-2018 at 09:21 AM. Reason: Formula ommitted
Trying to help ...
If you change the order of the questions, starting at the end
Last edited by José Augusto; 11-04-2018 at 09:26 AM. Reason: Same as previous post
In this case, use the formula offered in post #7.Originally Posted by ioswoody
Do you understand now why it won't work with that criterion at the end of the set of IF statements? If not, you must say, as this is a fundamental logic issue that you need to understand going forward.
No, Excel is not my gifting. Hence why I am asking for help on a forum website
To me we have asked: is it higher than 0, then is it equal to 0, then is it a bigger difference, then is it a smaller difference (which doesn't make sense in my head)
That does the trick - Thanks!Hi
It would help if the meaning of the columns was disclosed to understand what you are trying to achieve with "Closed"
The following solves the equation though
CheersPlease Login or Register to view this content.
When you put together a nested series of IF statements, Excel will deal with each one in turn. If any criterion = TRUE, it returns the required result and stops looking any further. In the case of R2, because J2<R2 (-11 is less than 5), it returns "Closing" - it will not go any futher because it has already found a fit.
This is why you have been advised to put the IF(R2>0,"Closed" criterion first.
I hope this helps you to understand how it works.
Last edited by AliGW; 11-04-2018 at 09:53 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Right, I understand now, with Closing and Closed being very similar (due to the nature of the question and the dataset, where the goal is to have R2 less than J2), you, in essence, have them at separate ends so both are engaged during the process.
In most cases, they are going to be Widening or Closing (whereas it will skip past the Closed option to find Closing where required)
So are you happy that the suggested tweak to the formula fulfills all your requirements? If so, please mark the thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks