Ask Your Question

Excel 2016 IFS function equivalent [closed]

asked 2017-03-31 22:52:15 +0200

WxManII gravatar image

updated 2017-03-31 22:56:20 +0200

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!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-28 21:19:25.243998


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.

Lupp gravatar imageLupp ( 2017-03-31 23:48:45 +0200 )edit

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?

WxManII gravatar imageWxManII ( 2017-04-01 00:14:02 +0200 )edit

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 gravatar imageLupp ( 2017-04-01 00:47:28 +0200 )edit

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

Lupp gravatar imageLupp ( 2017-04-01 13:06:40 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-03-31 23:31:34 +0200

Lupp gravatar image

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

edit flag offensive delete link more

answered 2017-04-01 00:07:33 +0200

WxManII gravatar image

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

edit flag offensive delete link more


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 gravatar imageLupp ( 2017-04-01 00:37:48 +0200 )edit

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,

Lupp gravatar imageLupp ( 2017-04-01 13:12:00 +0200 )edit

@WxManII This ain't an answer ! Edit the post itself !

rautamiekka gravatar imagerautamiekka ( 2017-04-19 23:06:30 +0200 )edit

Question Tools

1 follower


Asked: 2017-03-31 22:52:15 +0200

Seen: 1,779 times

Last updated: Apr 01 '17