Excel 2016 IFS function equivalent

[WxManII]

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:

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

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!

[Lupp]

As 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 Comments 15, 16. This seems to block a sensible fixing of the bug.

[WxManII]

I’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?

[Lupp]

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

[Lupp]

Would you mind to consider to read this appeal? Thanks in advance.

[Lupp]

IFS is implemented in LibO Calc as of V 5.2.0 released 2016-08-07.
See also tdf#97831.

[WxManII]

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!

Regards,
WxManII

[Lupp]

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

[Lupp]

Please 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,

[rautamiekka]

@WxManii This ain’t an answer ! Edit the post itself !