Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 19 Apr 2017 23:06:30 +0200Excel 2016 IFS function equivalenthttps://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/Hi folks. I'm working on a spreadsheet for repeater frequencies and would like to have the spreadsheet calculate and display the offsets automatically. I started designing the sheet in Excel 2016 and used the "IFS" function instead of nesting IF statements. I'm wondering how one would write "=IFS(AND(B4>145.1,B4<145.5),B4-0.6,AND(B4>146,B4<146.4),B4+0.6,AND(B4>146.6,B4<147),B4-0.6,AND(B4>147,B4<147.4),B4+0.6,AND(B4>147.6,B4<148),B4-0.6,TRUE,"Simplex")" in LibreOffice.
The parameters I'm trying to meet are in this table:
<pre>
2-Meter Repeater Output Frequency Standard Input Frequency Offset
145.1 MHz - 145.5 MHz -600 kHz
146.0 MHz - 146.4 MHz +600 kHz
146.6 MHz - 147.0 MHz -600 kHz
147.0 MHz - 147.4 MHz +600 kHz
147.6 MHz - 148.0 MHz -600 kHz
</pre>
The task I wish to perform is that I enter the output frequency and the spreadsheet calculates the input frequency.
FWIW, the answer to this question could also be useful in another sheet I'm trying to design that will color code cells based on results of blood pressure and heart rate readings.
Thanks in advance!Fri, 31 Mar 2017 22:52:15 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/Comment by Lupp for <p>Hi folks. I'm working on a spreadsheet for repeater frequencies and would like to have the spreadsheet calculate and display the offsets automatically. I started designing the sheet in Excel 2016 and used the "IFS" function instead of nesting IF statements. I'm wondering how one would write "=IFS(AND(B4>145.1,B4<145.5),B4-0.6,AND(B4>146,B4<146.4),B4+0.6,AND(B4>146.6,B4<147),B4-0.6,AND(B4>147,B4<147.4),B4+0.6,AND(B4>147.6,B4<148),B4-0.6,TRUE,"Simplex")" in LibreOffice.</p>
<p>The parameters I'm trying to meet are in this table:</p>
<pre>2-Meter Repeater Output Frequency Standard Input Frequency Offset
145.1 MHz - 145.5 MHz -600 kHz
146.0 MHz - 146.4 MHz +600 kHz
146.6 MHz - 147.0 MHz -600 kHz
147.0 MHz - 147.4 MHz +600 kHz
147.6 MHz - 148.0 MHz -600 kHz
</pre>
<p>The task I wish to perform is that I enter the output frequency and the spreadsheet calculates the input frequency. </p>
<p>FWIW, the answer to this question could also be useful in another sheet I'm trying to design that will color code cells based on results of blood pressure and heart rate readings.</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91812#post-id-91812As you seem to have access to Excel 2016 I dare ask you to definitely answer the question if TEXTJOIN and CONCAT actually do as once described: Called for array evaluation like in (e.g.) {=TEXTJOIN(",";1;A1:A10&Z1:Z10)} they were told to return an array of 10 times the same concatenation.
Even of Excel I cannot believe that it acts so absurdly.
See also [tdf#99625](https://bugs.documentfoundation.org/show_bug.cgi?id=99625) Comments 15, 16. This seems to block a sensible fixing of the bug.Fri, 31 Mar 2017 23:48:45 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91812#post-id-91812Comment by WxManII for <p>Hi folks. I'm working on a spreadsheet for repeater frequencies and would like to have the spreadsheet calculate and display the offsets automatically. I started designing the sheet in Excel 2016 and used the "IFS" function instead of nesting IF statements. I'm wondering how one would write "=IFS(AND(B4>145.1,B4<145.5),B4-0.6,AND(B4>146,B4<146.4),B4+0.6,AND(B4>146.6,B4<147),B4-0.6,AND(B4>147,B4<147.4),B4+0.6,AND(B4>147.6,B4<148),B4-0.6,TRUE,"Simplex")" in LibreOffice.</p>
<p>The parameters I'm trying to meet are in this table:</p>
<pre>2-Meter Repeater Output Frequency Standard Input Frequency Offset
145.1 MHz - 145.5 MHz -600 kHz
146.0 MHz - 146.4 MHz +600 kHz
146.6 MHz - 147.0 MHz -600 kHz
147.0 MHz - 147.4 MHz +600 kHz
147.6 MHz - 148.0 MHz -600 kHz
</pre>
<p>The task I wish to perform is that I enter the output frequency and the spreadsheet calculates the input frequency. </p>
<p>FWIW, the answer to this question could also be useful in another sheet I'm trying to design that will color code cells based on results of blood pressure and heart rate readings.</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91817#post-id-91817I've never used the TEXTJOIN function, my use of CONCATENATE was essentially to manipulate columns of text in order to create a batch file. I certainly don't qualify as an expert.
Perhaps if you can detail exactly what it is you want to do with these functions I can tinker with it in Excel 2016 and see if I can determine what behaviour it returns?Sat, 01 Apr 2017 00:14:02 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91817#post-id-91817Comment by Lupp for <p>Hi folks. I'm working on a spreadsheet for repeater frequencies and would like to have the spreadsheet calculate and display the offsets automatically. I started designing the sheet in Excel 2016 and used the "IFS" function instead of nesting IF statements. I'm wondering how one would write "=IFS(AND(B4>145.1,B4<145.5),B4-0.6,AND(B4>146,B4<146.4),B4+0.6,AND(B4>146.6,B4<147),B4-0.6,AND(B4>147,B4<147.4),B4+0.6,AND(B4>147.6,B4<148),B4-0.6,TRUE,"Simplex")" in LibreOffice.</p>
<p>The parameters I'm trying to meet are in this table:</p>
<pre>2-Meter Repeater Output Frequency Standard Input Frequency Offset
145.1 MHz - 145.5 MHz -600 kHz
146.0 MHz - 146.4 MHz +600 kHz
146.6 MHz - 147.0 MHz -600 kHz
147.0 MHz - 147.4 MHz +600 kHz
147.6 MHz - 148.0 MHz -600 kHz
</pre>
<p>The task I wish to perform is that I enter the output frequency and the spreadsheet calculates the input frequency. </p>
<p>FWIW, the answer to this question could also be useful in another sheet I'm trying to design that will color code cells based on results of blood pressure and heart rate readings.</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91820#post-id-91820CONCAT is rather different from CONCATENATE. CONCAT, like TEXTJOIN, is made to allow for the accumulation of an arbitrary number of text pieces given by range references where appropriate.
I will soon post an appeal concerning this, and would like to invite you personally to contribute. You surely won't need more expertise in anything than I needed expertise in repeaters to work on your question - and that was zero.Sat, 01 Apr 2017 00:47:28 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91820#post-id-91820Comment by Lupp for <p>Hi folks. I'm working on a spreadsheet for repeater frequencies and would like to have the spreadsheet calculate and display the offsets automatically. I started designing the sheet in Excel 2016 and used the "IFS" function instead of nesting IF statements. I'm wondering how one would write "=IFS(AND(B4>145.1,B4<145.5),B4-0.6,AND(B4>146,B4<146.4),B4+0.6,AND(B4>146.6,B4<147),B4-0.6,AND(B4>147,B4<147.4),B4+0.6,AND(B4>147.6,B4<148),B4-0.6,TRUE,"Simplex")" in LibreOffice.</p>
<p>The parameters I'm trying to meet are in this table:</p>
<pre>2-Meter Repeater Output Frequency Standard Input Frequency Offset
145.1 MHz - 145.5 MHz -600 kHz
146.0 MHz - 146.4 MHz +600 kHz
146.6 MHz - 147.0 MHz -600 kHz
147.0 MHz - 147.4 MHz +600 kHz
147.6 MHz - 148.0 MHz -600 kHz
</pre>
<p>The task I wish to perform is that I enter the output frequency and the spreadsheet calculates the input frequency. </p>
<p>FWIW, the answer to this question could also be useful in another sheet I'm trying to design that will color code cells based on results of blood pressure and heart rate readings.</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91865#post-id-91865Would you mind to consider to read [this appeal](https://ask.libreoffice.org/en/question/91825/can-someone-with-access-to-excel-2016-test-as-described-in-the-attachment-please/)? Thanks in advance.Sat, 01 Apr 2017 13:06:40 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91865#post-id-91865Answer by Lupp for <p>Hi folks. I'm working on a spreadsheet for repeater frequencies and would like to have the spreadsheet calculate and display the offsets automatically. I started designing the sheet in Excel 2016 and used the "IFS" function instead of nesting IF statements. I'm wondering how one would write "=IFS(AND(B4>145.1,B4<145.5),B4-0.6,AND(B4>146,B4<146.4),B4+0.6,AND(B4>146.6,B4<147),B4-0.6,AND(B4>147,B4<147.4),B4+0.6,AND(B4>147.6,B4<148),B4-0.6,TRUE,"Simplex")" in LibreOffice.</p>
<p>The parameters I'm trying to meet are in this table:</p>
<pre>2-Meter Repeater Output Frequency Standard Input Frequency Offset
145.1 MHz - 145.5 MHz -600 kHz
146.0 MHz - 146.4 MHz +600 kHz
146.6 MHz - 147.0 MHz -600 kHz
147.0 MHz - 147.4 MHz +600 kHz
147.6 MHz - 148.0 MHz -600 kHz
</pre>
<p>The task I wish to perform is that I enter the output frequency and the spreadsheet calculates the input frequency. </p>
<p>FWIW, the answer to this question could also be useful in another sheet I'm trying to design that will color code cells based on results of blood pressure and heart rate readings.</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?answer=91810#post-id-91810IFS is implemented in LibO Calc as of V 5.2.0 released 2016-08-07.
See also [tdf#97831](https://bugs.documentfoundation.org/show_bug.cgi?id=97831).Fri, 31 Mar 2017 23:31:34 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?answer=91810#post-id-91810Answer by WxManII for <p>Hi folks. I'm working on a spreadsheet for repeater frequencies and would like to have the spreadsheet calculate and display the offsets automatically. I started designing the sheet in Excel 2016 and used the "IFS" function instead of nesting IF statements. I'm wondering how one would write "=IFS(AND(B4>145.1,B4<145.5),B4-0.6,AND(B4>146,B4<146.4),B4+0.6,AND(B4>146.6,B4<147),B4-0.6,AND(B4>147,B4<147.4),B4+0.6,AND(B4>147.6,B4<148),B4-0.6,TRUE,"Simplex")" in LibreOffice.</p>
<p>The parameters I'm trying to meet are in this table:</p>
<pre>2-Meter Repeater Output Frequency Standard Input Frequency Offset
145.1 MHz - 145.5 MHz -600 kHz
146.0 MHz - 146.4 MHz +600 kHz
146.6 MHz - 147.0 MHz -600 kHz
147.0 MHz - 147.4 MHz +600 kHz
147.6 MHz - 148.0 MHz -600 kHz
</pre>
<p>The task I wish to perform is that I enter the output frequency and the spreadsheet calculates the input frequency. </p>
<p>FWIW, the answer to this question could also be useful in another sheet I'm trying to design that will color code cells based on results of blood pressure and heart rate readings.</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?answer=91816#post-id-91816Strange. I loaded the Excel sheet and LibreOffice Calc didn't handle that line properly at all. I'll have to check the version installed on my computer at home. Thanks for the answer, perhaps it's a formatting issue!
Regards,
WxManIISat, 01 Apr 2017 00:07:33 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?answer=91816#post-id-91816Comment by Lupp for <p>Strange. I loaded the Excel sheet and LibreOffice Calc didn't handle that line properly at all. I'll have to check the version installed on my computer at home. Thanks for the answer, perhaps it's a formatting issue!</p>
<p>Regards,
WxManII</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91819#post-id-91819I do not know your settings. But I would suspect your LibO Calc to not accept the comma as parameter delimiter. Until recently(?) the semicolon was mandatory in this role for all descendants of StarOffice (including LibO). Since I-dont-know-when a setting under 'Options'>'LibreOffice Calc'>'Formula' was introduced allowing to change that. Unnecessary to say that I deprecate it. And I do not know the default per locale/UI language.
Replace the commas by semicolons and your formula should work.Sat, 01 Apr 2017 00:37:48 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91819#post-id-91819Comment by Lupp for <p>Strange. I loaded the Excel sheet and LibreOffice Calc didn't handle that line properly at all. I'll have to check the version installed on my computer at home. Thanks for the answer, perhaps it's a formatting issue!</p>
<p>Regards,
WxManII</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91866#post-id-91866Please be aware of the fact that my answer included with the above comment **does not worry about formatting but about the parameter delimiter chosen in your settings**. If you want to replace commas by semicolons without doing it one by one, you can use the tool 'Find & Replace'. Additional advice available,Sat, 01 Apr 2017 13:12:00 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=91866#post-id-91866Comment by rautamiekka for <p>Strange. I loaded the Excel sheet and LibreOffice Calc didn't handle that line properly at all. I'll have to check the version installed on my computer at home. Thanks for the answer, perhaps it's a formatting issue!</p>
<p>Regards,
WxManII</p>
https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=93085#post-id-93085@WxManii This ain't an answer ! Edit the post itself !Wed, 19 Apr 2017 23:06:30 +0200https://ask.libreoffice.org/en/question/91806/excel-2016-ifs-function-equivalent/?comment=93085#post-id-93085