Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 10 Jul 2017 14:45:46 +0200problem with multiple if function and whole numbershttps://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/Hi! My first post here!
I'm creating a conversion tool which takes a code number (913, 858 or 471 for example) entered in one cell and converts that number to another code in an adjacent cell.
Example:
**Input Cell A1: 913**
**Output Cell B1: 3.1**
I've got about 50 input codes to deal with, so I opted to use a multiple IF function which I researched here and came up with something like this:
**Multiple IF in B Column:** =IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75)
However, since I needed to use SUM to tally the total, I used the VALUE function to convert the B Column output to numbers by doing this:
**Multiple IF in B Column with added VALUE:** =VALUE( IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75) )
The problem is that if the converted number is a whole number **( IF(A1=333,6.00) )**, that output is very odd. Depending upon where in the code that whole number is, the output could be 60, 6000, or in one instance, it output 0000600.
My question is this: How do I overcome this issue with whole numbers within this function?
Thank you all!
PS. I'm only capable of the most basic coding chores, so hopefully your response will utilize my present code, but if there is an equally simple way to do this, I'm open to another method as well.Sun, 09 Jul 2017 20:59:07 +0200https://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/Answer by librebel for <p>Hi! My first post here!</p>
<p>I'm creating a conversion tool which takes a code number (913, 858 or 471 for example) entered in one cell and converts that number to another code in an adjacent cell. </p>
<p>Example:</p>
<p><strong>Input Cell A1: 913</strong></p>
<p><strong>Output Cell B1: 3.1</strong></p>
<p>I've got about 50 input codes to deal with, so I opted to use a multiple IF function which I researched here and came up with something like this:</p>
<p><strong>Multiple IF in B Column:</strong> =IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75)</p>
<p>However, since I needed to use SUM to tally the total, I used the VALUE function to convert the B Column output to numbers by doing this:</p>
<p><strong>Multiple IF in B Column with added VALUE:</strong> =VALUE( IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75) )</p>
<p>The problem is that if the converted number is a whole number <strong>( IF(A1=333,6.00) )</strong>, that output is very odd. Depending upon where in the code that whole number is, the output could be 60, 6000, or in one instance, it output 0000600.</p>
<p>My question is this: How do I overcome this issue with whole numbers within this function?</p>
<p>Thank you all!</p>
<p>PS. I'm only capable of the most basic coding chores, so hopefully your response will utilize my present code, but if there is an equally simple way to do this, I'm open to another method as well.</p>
https://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/?answer=109823#post-id-109823Hello @ggatlanta,
Try if setting the “Number Format” for column B helps:
1. Select column B by clicking on the column header,
2. Right-click in column B and select “Format Cells...”,
3. in the dialog that pops up, select the tab called “Numbers”,
4. Type “0.00” ( without the quotes ) in the textbox called “Format code”
5. Press OK.Mon, 10 Jul 2017 02:45:15 +0200https://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/?answer=109823#post-id-109823Answer by Lupp for <p>Hi! My first post here!</p>
<p>I'm creating a conversion tool which takes a code number (913, 858 or 471 for example) entered in one cell and converts that number to another code in an adjacent cell. </p>
<p>Example:</p>
<p><strong>Input Cell A1: 913</strong></p>
<p><strong>Output Cell B1: 3.1</strong></p>
<p>I've got about 50 input codes to deal with, so I opted to use a multiple IF function which I researched here and came up with something like this:</p>
<p><strong>Multiple IF in B Column:</strong> =IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75)</p>
<p>However, since I needed to use SUM to tally the total, I used the VALUE function to convert the B Column output to numbers by doing this:</p>
<p><strong>Multiple IF in B Column with added VALUE:</strong> =VALUE( IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75) )</p>
<p>The problem is that if the converted number is a whole number <strong>( IF(A1=333,6.00) )</strong>, that output is very odd. Depending upon where in the code that whole number is, the output could be 60, 6000, or in one instance, it output 0000600.</p>
<p>My question is this: How do I overcome this issue with whole numbers within this function?</p>
<p>Thank you all!</p>
<p>PS. I'm only capable of the most basic coding chores, so hopefully your response will utilize my present code, but if there is an equally simple way to do this, I'm open to another method as well.</p>
https://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/?answer=109803#post-id-109803As long as there isn't a clear specification of the assignment of secondary codes to the primary ones I cannot offer "positive" advice.
Negative: All your conditional expressions are of the type `IF(Condition;Number)` and thus incomplete: The else part is missing. The IF function must nonetheless return **something** also if the condition comes out FALSE. In the absence of the else (otherwise) part this will be the FALSE itself treated as 0 (zero) by the automatic conversion into text.
Generally consider a basical redesign of your attempts.
(The usage of fractional numbers for the secondary codes is a mystery to me. I cannot reasonably guess what you actually want to achieve. If you want to assign explicitly one specific code to any allowed input: Dispose of the IF-chain and use a lookup-table. )
Edit: Decided to attach **[this](/upfiles/14996760026053215.ods)** example I made yesterday.Sun, 09 Jul 2017 21:17:58 +0200https://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/?answer=109803#post-id-109803Answer by ggatlanta for <p>Hi! My first post here!</p>
<p>I'm creating a conversion tool which takes a code number (913, 858 or 471 for example) entered in one cell and converts that number to another code in an adjacent cell. </p>
<p>Example:</p>
<p><strong>Input Cell A1: 913</strong></p>
<p><strong>Output Cell B1: 3.1</strong></p>
<p>I've got about 50 input codes to deal with, so I opted to use a multiple IF function which I researched here and came up with something like this:</p>
<p><strong>Multiple IF in B Column:</strong> =IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75)</p>
<p>However, since I needed to use SUM to tally the total, I used the VALUE function to convert the B Column output to numbers by doing this:</p>
<p><strong>Multiple IF in B Column with added VALUE:</strong> =VALUE( IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75) )</p>
<p>The problem is that if the converted number is a whole number <strong>( IF(A1=333,6.00) )</strong>, that output is very odd. Depending upon where in the code that whole number is, the output could be 60, 6000, or in one instance, it output 0000600.</p>
<p>My question is this: How do I overcome this issue with whole numbers within this function?</p>
<p>Thank you all!</p>
<p>PS. I'm only capable of the most basic coding chores, so hopefully your response will utilize my present code, but if there is an equally simple way to do this, I'm open to another method as well.</p>
https://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/?answer=109900#post-id-109900Thanks, all. I ended up redoing the spreadsheet in Google Sheet, which did not have this issue.
Thanks anyway.Mon, 10 Jul 2017 14:45:46 +0200https://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/?answer=109900#post-id-109900