[Solved] Multiple conditions one formula

[woodworks]

probably a simple solution exists. i was looking arround on the forum but none helped me to solve my issue.

in column B i have a list of numbers in the range of 1 to 12
i want in column F in each row a multi conditional return. y tryed this code but it gives only 1T as return no matter what number is in B3

Code: Select all

=IF(B3>0<4,"1T",IF(B3>4<7,"2T",IF(B3>6<10,"3T",IF(B3>9<13,"4T"))))

the "," delimiter is not the problem. my version of libre office does not accept the";" as delimiter

thanks

[MrProgrammer]

B3>0<4

Read [Tutorial] Order of Operations in Calc. Your expression will not do what you want. Work through how Calc evaluates that expression and you'll understand why the formula result is always 1T. There are two operators in the expression. They are evaluated independently, not concurrently. Think about their precedence and result type.

in column B i have a list of numbers in the range of 1 to 12. I want in column F in each row a multi conditional return.

See [Tutorial] VLOOKUP questions and answers, Q20/A20. You do not want to use IF functions.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

You have been asking lots of very common questions for beginners. Read [Tutorial] Ten concepts that every Calc user should know if you have not. Reread each day until you understand the material. Beginner questions are fine in the forum, but it will save you considerable time to get these concepts straight instead of asking for help every day. For example, the last section in the tutorial gives an example of using multiple conditions in a formula. Section 2 explains about TRUE and FALSE.

[woodworks]

You have been asking lots of very common questions for beginners. Read [Tutorial] Ten concepts that every Calc user should know if you have not. Reread each day until you understand the material. Beginner questions are fine in the forum, but it will save you considerable time to get these concepts straight instead of asking for help every day. For example, the last section in the tutorial gives an example of using multiple conditions in a formula. Section 2 explains about TRUE and FALSE.

thanks for your help and i am am aware that my questions are beginners questions. i made the effort to read your tutorials and made the formula work.nevertheless, i would have preferred in this case a direct solution such as a correction of my code, which i understand was a very simple thing.
i mentioned in a different thread that i am a woodworker and just have one very specific goal in mind: make some invoicing system for myself now that i switched to ubuntu, as on the internet i have not found what i needed. i am not interested in learning all that there is to learn about open office calc. i just needed this job to be done to get back to my workshop. so functional code is greatly appreciated and i am greatfull for comunities like this existing where knowledge is passed on and help is provided and i can tell you that i do so in my field of expertise. so if you ever want to know something about woodworking just let me know and i will give you all the practical answers you need unless you are interested in becomming a real woodworker, then i would also refer you to practice exercises and tutorials...

thanks

[jrkrideau]

Okay, I just chopped down this maple tree. How to I turn it into a table and six chairs.

It might be a good idea to upload the file to the forum for people to take a look at where you are at the moment. If you are new at spreadsheets it is really easy to lay out the data in a way that makes sense to a human but not necessarily to a computer (they are different).

We may be able to make a lot of practical suggestions or, just supply the required code, depending on which seems most reasonable.

Even better, but more difficult to set-up, would be a simple data base. Data bases are more stable, less error prone, and once set up much easier to use.

[Lupp]

...i would have preferred in this case a direct solution such as a correction of my code...

I can feel with you. But this was not that easy as you seem to think it was for more than one reasons. The given formula

Code: Select all

=IF(B3>0<4,"1T",IF(B3>4<7,"2T",IF(B3>6<10,"3T",IF(B3>9<13,"4T"))))

was not only wrong with respect to the way you used the comparators in a "twofold" expression like "B3>6<10" instead of combining the two comparisons using the AND function. But rectifying the formula only concerning this point would have resulted in

Code: Select all

=IF(AND(B3>0;B3<4);"1T";IF(AND(B3>4;B3<7);"2T";IF(AND(B3>6;B3<10);"3T";IF(AND(B3>9;B3<13);"4T"))))

and this is not a solution for any task I might guess you had in mind.
1. There are many possible (thinkable) values B3 can have that will not match one of the conditions at all - and there is no precaution taken for this case. The formula with its incomplete alternative in the last part applying the IF function would return the logical value FALSE then.
In more detail the unhandled cases are B3<=0, B3=4, and B3>=13.
2. On the other hand there are the ranges 6<B3<7 and 9<B3<10 (now in mathematical notation) for which two of your (double) conditions are satisfied. From the structure of the formula we can conclude that then only the first match will evaluate, but we (I) cannot know for sure that this is exactly what you want.
I would have to guess your intentions, and no "consultant" should rashly advise on such a base. If I did, my resuilt was:

Code: Select all

=IF(AND(0<B3;B3<=4);"1T";IF(AND(4<B3;B3<=6);"2T";IF(AND(6<B3;B3<=9);"3T";IF(AND(9<B3;B3<=12);"4T";"Inadmissible value!"))))

Whether it is likely or not that this might implement your intentions will depend, among other things, on the question whether or not you admit non-integer values for B3. (How the value 4 should be handled still only guessed.)

Next problem:
If the guesses were correct there remains as a matter of fact that the above solution is of rather low quality. We may accept that as we sometimes also may eat a bad Burger. For one advising here, this is not a way easily to go. He will think of possible contexts in your sheets where much more functionality will be implemented. And he will take in account possible impact of a low-quality-solution on efficiency and reliability in the context.
If one of the guesses was wrong basically, on the other hand, the only effect might be wasted time.

Better to wait for clarifications - or to leave the thing to you, possibly.

[karolus]

Code: Select all

=IF(AND(0<B3;B3<=4);"1T";IF(AND(4<B3;B3<=6);"2T";IF(AND(6<B3;B3<=9);"3T";IF(AND(9<B3;B3<=12);"4T";"Inadmissible value!"))))

WTF! If-Chains are evaluated from left to right -- no double check needed!

Code: Select all

=IF(B3<=4;"1T";IF(B3<=6;"2T";IF(B3<=9;"3T";IF(B3<=12;"4T";"Inadmissible value!"))))

and of course I would prefer a little "help-range" with limits left and "1T" to "4T" on the right to use simple VLOOKUP

[Lupp]

(@karolus) Did you also consider

Code: Select all

=CHOOSE(1+(B3>0)+(B3>4)+(B3>6)+(B3>9)+(B3>12);"Negative value!";"1T";"2T";"3T";"4T";"Excessive value!")

or with 3 replacing 4:

Code: Select all

=CHOOSE(1+(B3>0)+(B3>3)+(B3>6)+(B3>9)+(B3>12);"Negative value!";"1T";"2T";"3T";"4T";"Excessive value!")

and - if the threshold 4 is actually replaced with 3, and the thresholds are assured equidistant then:

Code: Select all

=CHOOSE(2+MIN(4;MAX(-1;INT(B3/3)));"Negative value!";"1T";"2T";"3T";"4T";"Excessive value!")

(Using a lookup table should be preferable, of course.)

[karolus]

Hallo

(@karolus) Did you also consider:
...
...

Only for some kind of Sophistication-contest but not for real Sheets to maintain.

[Lupp]

The first formula using CHOOSE at least avoids nested IFs and interleaved parentheses.

[woodworks]

Okay, I just chopped down this maple tree. How to I turn it into a table and six chairs.

It might be a good idea to upload the file to the forum for people to take a look at where you are at the moment. If you are new at spreadsheets it is really easy to lay out the data in a way that makes sense to a human but not necessarily to a computer (they are different).

We may be able to make a lot of practical suggestions or, just supply the required code, depending on which seems most reasonable.

Even better, but more difficult to set-up, would be a simple data base. Data bases are more stable, less error prone, and once set up much easier to use.

bring the trunk to a sawing mill, have them cut the trunk into planks and store these for 3 to 6 years to dry, then contact me again...
(make sure the planks are seperated and airflow/ ventilation is guaranteed).

i have finished the spreadsheet for invoicing and would upload it (last sheet is the manual) for anyone to use or improve...
but the file has 469k and only 128 are allowed. if anyone wants i can send it by email
when i did the switch to ubuntu i had three major concerns. which invoicing software, which techdrawing software and how to sync my ipod.
to find a very simple non-online invoicing system that includes some layout features seemed impossible. so for those out there programming, what i would upload i know is not the best way to do it. but it serves my needs and i believe it could serve many if maybe improved

[woodworks]

I forgott to mention,
THANKS FOR ALL HELP! as i said i got it done...

[MrProgrammer]

=IF(B3>0<4,"1T",IF(B3>4<7,"2T",IF(B3>6<10,"3T",IF(B3>9<13,"4T"))))

[Why does my formula give] 1T as return no matter what number is in B3?

The conditional expression in the first IF function is B3>0<4. It has two operators. The tutorial says that both are precedence 0, so they are evaluated left to right. B3>0 is always true because we are told that B3 is in the range 1 to 12. True has value 1 in Calc so the second operator evaluates 1<4. This is also true. Since the conditional expression is true for any of the specfied values in B3, the formula result is always 1T.

I would have preferred … a correction of my code.

Based on what you've written I'm going to make the simplifying assumption that "a list of numbers in the range of 1 to 12" means "a list of natural numbers in the range of 1 to 12". The first conditional expression presumably is intended to determine if B3 is 1, 2, or 3. We don't need to check for anything less than 1 because we know the allowed range is 1 to 12. So the first expression can be B3<4. As others have noted, the case where B3 is 4 is not covered by your formula. I will assume the intent is to map the next three natural numbers (4, 5, and 6) to 2T. We already know B4 is not 1, 2, or 3 since the second expression is in the OtherwiseValue part of the first IF function. So the second conditional expression should be B3<7. Similarly the third conditional expression will be B3<10. If B3 fails all three tests, we know it is 10, 11, or 12, so there is no need for a fourth IF function. The complete formula for OpenOffice is =IF(B3<4;"1T";IF(B3<7;"2T";IF(B3<10;"3T";"4T"))), as shown in the first sheet of the attachment.

But using nested IF functions quickly gets unwieldy. Suppose instead of 1T through 4T we have 1T though 40T. You won't want 39 nested IF functions even if Calc would accept that mess. The second sheet shows how to use a single VLOOKUP function and a table (in cells X1:Y4) to make the assignments. The formula is =VLOOKUP(B3;$X$1:$Y$4;2) and it can be extended to 40T by just making more table entries with a minor change to the formula. =VLOOKUP(B3;$X$1:$Y$40;2)

The third sheet shows how to use an array constant instead of a table, though this techinque doesn't extend as easily to 40T. LibreOffice syntax may be different than what I use in OpenOffice. However the attachment should show you the correct syntax in either case. =VLOOKUP(B3;{1;"1T"|4;"2T"|7;"3T"|10;"4T"};2)

In this particular case, the assignements follow a simple pattern: each T value corresponds to three sequential natural numbers and there are no gaps. The QUOTIENT function divides two numbers and discards the remainder to produce an integer. We can easily calculate the T values directly from the values in B3 as shown in the fourth sheet. =QUOTIENT(B3+2;3)&"T" When B3 is 5, adding 2 gives 7, and dividing by 3 gives 2 remainder 1. Concatenating 2 and "T" gives the result 2T.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[woodworks]

thanks, as i said i made it work. i had managed a solution that was not very efficient but result of taking part of my understanding of the tutorial very literal. the first logic is every condition has to be mentioned seperately, so i basically had 12 IF's.... this worked. i now changed that to

Code: Select all

=IF(B3<>"",IF(B3<4,"1T",IF(B3<7,"2T",IF(B3<10,"3T","4T"))))

following your suggestion but added B3<>"" as otherwise i got 1T as return when B3 is empty. now i get the return FALSE if B3 is empty. not elegant but works for me.
the spreadsheet has unfortunately 461k now in finished state so i can not upload it here, but as i said even though itś probably somewhat clumsy, going through forums looking for simple invoicing system something like this could be of great use to freelancers. many are looking for something like this. i know a database for this would be the right thing to use, but for someone not into programming, spreadsheet are in my opinion much easier to understand and set-up and modify.
if you want i can send the file by email so you can give an opinion. maybe i just did not look in the right direction and there is a ready-made thing like this for ubuntu on the internet

[Lupp]

You again have an incomplete alternative for the outermost IF. As I told somewhere above already, an IF() will treturn the logical value of the evaluated condition, if the respective parameter (ThenExpression or/and ElseExpression) is missing.
You need to complete the outermost IF() for getting it more "elegant".

Code: Select all

=IF(B3<>"";IF(B3<4;"1T";IF(B3<7;"2T";IF(B3<10;"3T";"4T")));"")

or (better imo)

Code: Select all

=IF(ISBLANK(B3);"";IF(B3<4;"1T";IF(B3<7;"2T";IF(B3<10;"3T";"4T"))))

Just out of curiosity:
1. Shall your formula also accept non-integers?
2. Why did you not accept the most preferable suggestion by MrProgrammer, to use a lookup table? Maybe you missed to regard the columns X and Y in the sheet 'VLOOKUP' of his attached example. There the lookup table was positioned.

[woodworks]

cool, thanks even better.
is there any way to uplioad 461k i would like to share the results

[RusselB]

Upload it to a file sharing site, like Dropbox, then post a link to the uploaded file

[woodworks]

well here it is... i put the last sheet as "manual" so you can understand how my way works.
again, i understand this for sure is clumsy and db would be better, but for an amateur who does not want to become a pro this works.
maybe everyone can help to make it better and i am sure many freelancers will be happy with this
https://www.dropbox.com/s/64yxeqab46ate ... y.ods?dl=0