[Solved] What am I doing wrong with this formula?
[Solved] What am I doing wrong with this formula?
[SOLVED WITH THANKS]
Hello The example below shows a long formula of five repeated similar (but different cells) "IF" statements that is meant to show a "1" if the criteria is met in any of the five scenarios, but it's not working as expected. A shorter formula cut out from the longer formula is shown underneath (IF statement 4) that does return a "1".
At the moment the longer formula returns "FALSE", not a 0, The "false" returns I can deal with but why isn't it showing a 1? Maybe something to do with a string of "IF" functions not working in this manner after one "IF"? Or maybe I haven't written some part of the formula correctly? The perplexing part is it seems to sometimes work and sometime not on other rows. Any help appreciated greatly.
IF(EW44=EX44;IF(EP44<=2;1;IF(EW44=EY44;IF(EQ44<=2;1;IF(EW44=EZ44;IF(ER44<=2;1;IF(EW44=FA44;IF(ES44<=2;1;IF(EW44=FB44;IF(ET44<=2;1;0)))))))))) -Returns "FALSE"
IF(EW44=FA44;IF(ES44<=2;1;0)) -Returns a 1
Hello The example below shows a long formula of five repeated similar (but different cells) "IF" statements that is meant to show a "1" if the criteria is met in any of the five scenarios, but it's not working as expected. A shorter formula cut out from the longer formula is shown underneath (IF statement 4) that does return a "1".
At the moment the longer formula returns "FALSE", not a 0, The "false" returns I can deal with but why isn't it showing a 1? Maybe something to do with a string of "IF" functions not working in this manner after one "IF"? Or maybe I haven't written some part of the formula correctly? The perplexing part is it seems to sometimes work and sometime not on other rows. Any help appreciated greatly.
IF(EW44=EX44;IF(EP44<=2;1;IF(EW44=EY44;IF(EQ44<=2;1;IF(EW44=EZ44;IF(ER44<=2;1;IF(EW44=FA44;IF(ES44<=2;1;IF(EW44=FB44;IF(ET44<=2;1;0)))))))))) -Returns "FALSE"
IF(EW44=FA44;IF(ES44<=2;1;0)) -Returns a 1
Last edited by Veda on Tue Aug 14, 2018 8:32 pm, edited 1 time in total.
Win 10, open office 4.1.5
Re: What am I doing wrong with this formula?
Test it by breaking it down in multiple smaller steps - you will spot where it is going wrong.
Is there a limit of how many "levels" you can have in an IF? If so break it into two with half the levels in each.
FALSE and 0 are the same.
Is there a limit of how many "levels" you can have in an IF? If so break it into two with half the levels in each.
FALSE and 0 are the same.
Last edited by John_Ha on Tue Aug 14, 2018 3:40 pm, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Re: What am I doing wrong with this formula?
If it works on some rows but not on others, then the obvious course is to verify the data types, using /View /Value Highlighting.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: What am I doing wrong with this formula?
What do you want to test? To reach the inner conditions (which you extracted), your formula requires all elements in EX44:FB44 to equal EW44. Otherwise, FALSE will be returned.
Did you perhaps mean this?
This can be better solved using MATCH() and INDEX()
Did you perhaps mean this?
Code: Select all
=IF(EW44=EX44;IF(EP44<=2;1;0);IF(EW44=EY44;IF(EQ44<=2;1;0);IF(EW44=EZ44;IF(ER44<=2;1;0);IF(EW44=FA44;IF(ES44<=2;1;0);IF(EW44=FB44;IF(ET44<=2;1;0)))))))
Code: Select all
=IF(ISERROR(MATCH(EW44;EX44:FB44;0));0;IF(INDEX(EP44:ET44;1;MATCH(EW44;EX44:FB44;0))<=2;1;0))
Re: What am I doing wrong with this formula?
keme wrote:What do you want to test? To reach the inner conditions (which you extracted), your formula requires all elements in EX44:FB44 to equal EW44. Otherwise, FALSE will be returned.
Did you perhaps mean this?This can be better solved using MATCH() and INDEX()Code: Select all
=IF(EW44=EX44;IF(EP44<=2;1;0);IF(EW44=EY44;IF(EQ44<=2;1;0);IF(EW44=EZ44;IF(ER44<=2;1;0);IF(EW44=FA44;IF(ES44<=2;1;0);IF(EW44=FB44;IF(ET44<=2;1;0)))))))
Code: Select all
=IF(ISERROR(MATCH(EW44;EX44:FB44;0));0;IF(INDEX(EP44:ET44;1;MATCH(EW44;EX44:FB44;0))<=2;1;0))
Hi, "Match" and "Index" are too advanced for me but I tried your other solution where each IF is a separate function. It originally gave an error but that was auto-fixed and now it does work cleaner and solved the formula above. However the same problem is arising on other rows, example.
=IF(EW46=EX46;IF(EP46<=2;1;0);IF(EW46=EY46;IF(EQ46<=2;1;0);IF(EW46=EZ46;IF(ER46<=2;1;0);IF(EW46=FA46;IF(ES46<=2;1;0);IF(EW46=FB46;IF(ET46<=2;1;0))))))
The above returns a 0 but if I cut/paste =IF(EW46=EY46;IF(EQ46<=2;1;0)) from the above formula just this bit returns a "1" as it should because the criteria matches this IF statement. I just can't see why it's doing this wrong in the long formula.
Win 10, open office 4.1.5
Re: What am I doing wrong with this formula?
Hi, I have checked the data many times, it is only numerical and there are only a 10 columns to check. I can't think of what else might be causing problems. I will probably have to assemble and check every stage to see where it goes wrong.RoryOF wrote:If it works on some rows but not on others, then the obvious course is to verify the data types, using /View /Value Highlighting.
Win 10, open office 4.1.5
Re: What am I doing wrong with this formula?
The only reason I can see is that EW46 also equals EX46, and EP46 is more than 2.
If you need the first point where both conditions match, you need to use IF(AND(...) ;... ) instead of nested IF().
It is probably easier to help you if you explain what you want to achieve instead of having us blindly guessing what you may have meant.
If you need the first point where both conditions match, you need to use IF(AND(...) ;... ) instead of nested IF().
It is probably easier to help you if you explain what you want to achieve instead of having us blindly guessing what you may have meant.
Re: What am I doing wrong with this formula?
You are right in your assumption that EW46 is greater than 2 (it's 11) but if it is a case of a chronological error issue then EW44 would not be working either, and it is. That why I don't understand what's happening.keme wrote:The only reason I can see is that EW46 also equals EX46, and EP46 is more than 2.
If you need the first point where both conditions match, you need to use IF(AND(...) ;... ) instead of nested IF().
It is probably easier to help you if you explain what you want to achieve instead of having us blindly guessing what you may have meant.
What do you mean exactly explain what I want to achieve? Please forgive me I really don't understand what you mean as the formula is explaining what I want to achieve. The idea is if the first parameter matches (=) another parameter then it checks a third third parameter to see if it's <=2 and if it is then return a 1 or if not a 0. If you mean something else please tell me, I am still learning all this and really appreciated the help.
EDIT: Wait, maybe it has to do with the AND function as you mention. Ah, I bet that's it, I will try that and let you know.
Win 10, open office 4.1.5
Re: What am I doing wrong with this formula?
Hi again, well I added AND but now everything returns a "FALSE", lol, hair is falling out. Is the AND the right way and place?keme wrote:The only reason I can see is that EW46 also equals EX46, and EP46 is more than 2.
If you need the first point where both conditions match, you need to use IF(AND(...) ;... ) instead of nested IF().
It is probably easier to help you if you explain what you want to achieve instead of having us blindly guessing what you may have meant.
=IF(EW46=EX46;IF(AND(EP46<=2;1;0);IF(EW46=EY46;IF(AND(EQ46<=2;1;0);IF(EW46=EZ46;IF(AND(ER46<=2;1;0);IF(EW46=FA46;IF(AND(ES46<=2;1;0);IF(EW46=FB46;IF(AND(ET46<=2;1;0)))))))))))
Or does the AND go in after the first IF of each one? EDIT: I tried that also but same return, all "FALSE".
=IF(AND(EW43=EX43;IF(EP43<=2;1;0);IF(AND(EW43=EY43;IF(EQ43<=2;1;0);IF(AND(EW43=EZ43;IF(ER43<=2;1;0);IF(AND(EW43=FA43;IF(ES43<=2;1;0);IF(AND(EW43=FB43;IF(ET43<=2;1;0)))))))))))
Win 10, open office 4.1.5
Re: What am I doing wrong with this formula?
Maybe you need but we can only guess if we don't know the context.
Code: Select all
=IF(AND(EW43=EX43;EP43<=2);1;IF(AND(EW43=EY43;EQ43<=2);1;IF(AND(EW43=EZ43;ER43<=2);1;IF(AND(EW43=FA43;ES43<=2);1;IF(AND(EW43=FB43;ET43<=2);1;0)))))
AOO 4.1.15 & LO 24.2 on Windows 10
-
- Posts: 68
- Joined: Wed Apr 19, 2017 9:22 pm
Re: What am I doing wrong with this formula?
OpenOffice 4.1.1 on Windows 7
Re: What am I doing wrong with this formula?
Hi Alex1, you got it spot on perfect! I can see what you've done with the "AND", plus putting all the "then" conditions into the brackets, very nice. I can finally move on with my project after many hours. Also I will learn from this formula for the future so I thank you not only for the answer but also for a great tutorial.Alex1 wrote:Maybe you needbut we can only guess if we don't know the context.Code: Select all
=IF(AND(EW43=EX43;EP43<=2);1;IF(AND(EW43=EY43;EQ43<=2);1;IF(AND(EW43=EZ43;ER43<=2);1;IF(AND(EW43=FA43;ES43<=2);1;IF(AND(EW43=FB43;ET43<=2);1;0)))))
Cheers
Rob
Win 10, open office 4.1.5
Re: What am I doing wrong with this formula?
Hi Keme, just wanted to say thanks to you also as you did give me the right direction with the AND but I just didn't understand how to write it properly. Anyway, goal accomplished, thanks againkeme wrote:The only reason I can see is that EW46 also equals EX46, and EP46 is more than 2.
If you need the first point where both conditions match, you need to use IF(AND(...) ;... ) instead of nested IF().
It is probably easier to help you if you explain what you want to achieve instead of having us blindly guessing what you may have meant.
Win 10, open office 4.1.5