Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 08 Sep 2017 15:47:13 +0200Need formula using ifshttps://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/ Have a range of numbers 1 - 60.
I want to make a choice depending on a range of numbers i.e.
If the number is between 1 and 10 then do THIS
if the number is between 11and 20 do THAT
if the number is between 21 and 40 do SOMETHING ELSE
if the number is between 41 and 50 do XYZ
if the number is between 51 and 60 do ABC
The THIS/THATetc names relate to formula.
Thu, 07 Sep 2017 22:52:57 +0200https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/Answer by JohnSUN for <p>Have a range of numbers 1 - 60.
I want to make a choice depending on a range of numbers i.e.
If the number is between 1 and 10 then do THIS
if the number is between 11and 20 do THAT
if the number is between 21 and 40 do SOMETHING ELSE
if the number is between 41 and 50 do XYZ
if the number is between 51 and 60 do ABC</p>
<p>The THIS/THATetc names relate to formula.</p>
https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?answer=129504#post-id-129504You are in vain trying to apply the function of the **IFS()** for this task - of course, in this way the solution can be obtained, but it will be very difficult and cumbersome. Try any of these solutions.
=CHOOSE(INT((A1-1)/10)+1;"THIS";"THAT";"SOMETHING ELSE";"SOMETHING ELSE";"XYZ";"ABC")
or
=VLOOKUP(A1;{1;"THIS"|11;"THAT"|21;"SOMETHING ELSE"|41;"XYZ"|51;"ABC"};2;1)
There A1 is your value and "THIS"-"THAT"-"SOMETHING ELSE"-"XYZ"-"ABC" your actions for each case
**Update** Other variants for writing the last solution:
=HLOOKUP(A1;{1;11;21;41;51|"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"};2;1)
=LOOKUP(A1;{1;11;21;41;51};{"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"})
Perhaps with this arrangement of values and actions the formula will be easier to edit.
Considering comment of esteemed colleague @robleyd, I bring also the variant of the formula using a set of nested IF().
=IF(A1<1;NA();IF(A1<11;"THIS";IF(A1<21;"THAT";IF(A1<41;"SOMETHING ELSE";IF(A1<51;"XYZ";IF(A1<61;"ABC";NA()))))))
You have to admit that the work of editing the formula very difficult in such a record.
![Don't write "Thank you"](/upfiles/15048515233254362.gif)Fri, 08 Sep 2017 08:19:20 +0200https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?answer=129504#post-id-129504Comment by JohnSUN for <p>You are in vain trying to apply the function of the <strong>IFS()</strong> for this task - of course, in this way the solution can be obtained, but it will be very difficult and cumbersome. Try any of these solutions.</p>
<pre><code>=CHOOSE(INT((A1-1)/10)+1;"THIS";"THAT";"SOMETHING ELSE";"SOMETHING ELSE";"XYZ";"ABC")
</code></pre>
<p>or</p>
<pre><code>=VLOOKUP(A1;{1;"THIS"|11;"THAT"|21;"SOMETHING ELSE"|41;"XYZ"|51;"ABC"};2;1)
</code></pre>
<p>There A1 is your value and "THIS"-"THAT"-"SOMETHING ELSE"-"XYZ"-"ABC" your actions for each case</p>
<p><strong>Update</strong> Other variants for writing the last solution:</p>
<pre><code>=HLOOKUP(A1;{1;11;21;41;51|"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"};2;1)
=LOOKUP(A1;{1;11;21;41;51};{"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"})
</code></pre>
<p>Perhaps with this arrangement of values and actions the formula will be easier to edit.</p>
<p>Considering comment of esteemed colleague <a href="/en/users/32456/robleyd/">@robleyd</a>, I bring also the variant of the formula using a set of nested IF(). </p>
<pre><code>=IF(A1<1;NA();IF(A1<11;"THIS";IF(A1<21;"THAT";IF(A1<41;"SOMETHING ELSE";IF(A1<51;"XYZ";IF(A1<61;"ABC";NA()))))))
</code></pre>
<p>You have to admit that the work of editing the formula very difficult in such a record.
<img alt="Don't write "Thank you"" src="/upfiles/15048515233254362.gif"></p>
https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?comment=129606#post-id-129606@Mike Kaganski You are absolutely right. But when I read "The THIS/THATetc names relate to formula", I thought that "ABC" may well turn out to be the same NA(). And why do we need so many NA() in a single formula? ;-)Fri, 08 Sep 2017 15:47:13 +0200https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?comment=129606#post-id-129606Comment by Mike Kaganski for <p>You are in vain trying to apply the function of the <strong>IFS()</strong> for this task - of course, in this way the solution can be obtained, but it will be very difficult and cumbersome. Try any of these solutions.</p>
<pre><code>=CHOOSE(INT((A1-1)/10)+1;"THIS";"THAT";"SOMETHING ELSE";"SOMETHING ELSE";"XYZ";"ABC")
</code></pre>
<p>or</p>
<pre><code>=VLOOKUP(A1;{1;"THIS"|11;"THAT"|21;"SOMETHING ELSE"|41;"XYZ"|51;"ABC"};2;1)
</code></pre>
<p>There A1 is your value and "THIS"-"THAT"-"SOMETHING ELSE"-"XYZ"-"ABC" your actions for each case</p>
<p><strong>Update</strong> Other variants for writing the last solution:</p>
<pre><code>=HLOOKUP(A1;{1;11;21;41;51|"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"};2;1)
=LOOKUP(A1;{1;11;21;41;51};{"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"})
</code></pre>
<p>Perhaps with this arrangement of values and actions the formula will be easier to edit.</p>
<p>Considering comment of esteemed colleague <a href="/en/users/32456/robleyd/">@robleyd</a>, I bring also the variant of the formula using a set of nested IF(). </p>
<pre><code>=IF(A1<1;NA();IF(A1<11;"THIS";IF(A1<21;"THAT";IF(A1<41;"SOMETHING ELSE";IF(A1<51;"XYZ";IF(A1<61;"ABC";NA()))))))
</code></pre>
<p>You have to admit that the work of editing the formula very difficult in such a record.
<img alt="Don't write "Thank you"" src="/upfiles/15048515233254362.gif"></p>
https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?comment=129602#post-id-129602WRT your updated multiple-IFs variant: (nerd mode on) for your brilliant *LOOKUP solutions to be equivalent to it, you should put them into IF(A1>60;NA();VLOOKUP(...)) ;-)Fri, 08 Sep 2017 15:26:28 +0200https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?comment=129602#post-id-129602Comment by robleyd for <p>You are in vain trying to apply the function of the <strong>IFS()</strong> for this task - of course, in this way the solution can be obtained, but it will be very difficult and cumbersome. Try any of these solutions.</p>
<pre><code>=CHOOSE(INT((A1-1)/10)+1;"THIS";"THAT";"SOMETHING ELSE";"SOMETHING ELSE";"XYZ";"ABC")
</code></pre>
<p>or</p>
<pre><code>=VLOOKUP(A1;{1;"THIS"|11;"THAT"|21;"SOMETHING ELSE"|41;"XYZ"|51;"ABC"};2;1)
</code></pre>
<p>There A1 is your value and "THIS"-"THAT"-"SOMETHING ELSE"-"XYZ"-"ABC" your actions for each case</p>
<p><strong>Update</strong> Other variants for writing the last solution:</p>
<pre><code>=HLOOKUP(A1;{1;11;21;41;51|"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"};2;1)
=LOOKUP(A1;{1;11;21;41;51};{"THIS";"THAT";"SOMETHING ELSE";"XYZ";"ABC"})
</code></pre>
<p>Perhaps with this arrangement of values and actions the formula will be easier to edit.</p>
<p>Considering comment of esteemed colleague <a href="/en/users/32456/robleyd/">@robleyd</a>, I bring also the variant of the formula using a set of nested IF(). </p>
<pre><code>=IF(A1<1;NA();IF(A1<11;"THIS";IF(A1<21;"THAT";IF(A1<41;"SOMETHING ELSE";IF(A1<51;"XYZ";IF(A1<61;"ABC";NA()))))))
</code></pre>
<p>You have to admit that the work of editing the formula very difficult in such a record.
<img alt="Don't write "Thank you"" src="/upfiles/15048515233254362.gif"></p>
https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?comment=129565#post-id-129565I wonder if the question meant using a number of nested IF statements ?Fri, 08 Sep 2017 10:19:17 +0200https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?comment=129565#post-id-129565Answer by dogluvver for <p>Have a range of numbers 1 - 60.
I want to make a choice depending on a range of numbers i.e.
If the number is between 1 and 10 then do THIS
if the number is between 11and 20 do THAT
if the number is between 21 and 40 do SOMETHING ELSE
if the number is between 41 and 50 do XYZ
if the number is between 51 and 60 do ABC</p>
<p>The THIS/THATetc names relate to formula.</p>
https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?answer=129590#post-id-129590Thx - I had an ah-hah moment in the middle of the night and came up with the vlookup solution.
Fri, 08 Sep 2017 13:50:05 +0200https://ask.libreoffice.org/en/question/129457/need-formula-using-ifs/?answer=129590#post-id-129590