[Solved] What am I doing wrong with this formula?

Discuss the spreadsheet application
Post Reply
Veda
Posts: 66
Joined: Mon Apr 16, 2018 1:18 am

[Solved] What am I doing wrong with this formula?

Post by Veda »

[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
Last edited by Veda on Tue Aug 14, 2018 8:32 pm, edited 1 time in total.
Win 10, open office 4.1.5
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: What am I doing wrong with this formula?

Post by John_Ha »

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.
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.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: What am I doing wrong with this formula?

Post by RoryOF »

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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: What am I doing wrong with this formula?

Post by keme »

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?

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)))))))
This can be better solved using MATCH() and INDEX()

Code: Select all

=IF(ISERROR(MATCH(EW44;EX44:FB44;0));0;IF(INDEX(EP44:ET44;1;MATCH(EW44;EX44:FB44;0))<=2;1;0))
Veda
Posts: 66
Joined: Mon Apr 16, 2018 1:18 am

Re: What am I doing wrong with this formula?

Post by Veda »

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?

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)))))))
This can be better solved using MATCH() and INDEX()

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
Veda
Posts: 66
Joined: Mon Apr 16, 2018 1:18 am

Re: What am I doing wrong with this formula?

Post by Veda »

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.
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.
Win 10, open office 4.1.5
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: What am I doing wrong with this formula?

Post by keme »

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.
Veda
Posts: 66
Joined: Mon Apr 16, 2018 1:18 am

Re: What am I doing wrong with this formula?

Post by Veda »

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.
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.

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
Veda
Posts: 66
Joined: Mon Apr 16, 2018 1:18 am

Re: What am I doing wrong with this formula?

Post by Veda »

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.
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?

=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
Alex1
Volunteer
Posts: 723
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: What am I doing wrong with this formula?

Post by Alex1 »

Maybe you need

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)))))
but we can only guess if we don't know the context.
AOO 4.1.15 & LO 24.2 on Windows 10
Bald Eagle
Posts: 68
Joined: Wed Apr 19, 2017 9:22 pm

Re: What am I doing wrong with this formula?

Post by Bald Eagle »

Try this as a guide to work out the problem:

viewtopic.php?f=75&t=92044
OpenOffice 4.1.1 on Windows 7
Veda
Posts: 66
Joined: Mon Apr 16, 2018 1:18 am

Re: What am I doing wrong with this formula?

Post by Veda »

Alex1 wrote:Maybe you need

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)))))
but we can only guess if we don't know the context.
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. :D
Cheers
Rob
Win 10, open office 4.1.5
Veda
Posts: 66
Joined: Mon Apr 16, 2018 1:18 am

Re: What am I doing wrong with this formula?

Post by Veda »

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.
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 again :D
Win 10, open office 4.1.5
Post Reply