Syntax for nested CONCAT and ROUND functions in one cell

asked 2020-07-28 13:38:22 +0200

GrahamLees
13 ●2 ●3 ●7

updated 2020-08-09 19:55:24 +0200

Alex Kemp

flag of United Kingdom of Great Britain and Northern Ireland

20989 ●21 ●60 ●60 http://etmg.altervista...

I have two formulas which work individually as follows:

=CONCAT("m³=",ROUND((197.45-55.41)/86,2))
=(ROUND(ROUND(197.45-55.41,2)/ROUND(86,2)/1000,5))

My difficulty lies in finding the correct syntax to join these together to form a result in a single cell.

What I have tried:

=CONCAT("m³=",ROUND((197.45-55.41)/86,2))," soit ",(ROUND(ROUND(197.45-55.41,2)/ROUND(86,2)/1000,5))

but it fails to produce a correct result as the syntax appears wrong. There is no error code as such - the equation is merely pasted as text into the cell.

The first formula produces: 1.65 (from the derived cells)
The second formula produces: 0.00165 (from the derived cells) The desired text result in a single cell is: m³=1.65 makes 0.00167€/litre

For reference purposes, the values provided are from individual cells (so e.g. 197.45 is the result from specific calculated cells)

image description

What is the correct syntax to achieve this?

edit retag flag offensive close merge delete

Comments

Quoting @GrahamLees: "There is no error code as such - the equation is merely pasted as text into the cell."
I didn't analyze the funny (much too complicated) formula, but if a formula doesn't produce anything (result or error) in a cell, but is simply displayed as entered, it is obviously taken as a text.
Generally thsi is expected only if the cell is set to NOT RECOGNIZE numbers or formulas, Open the formatting dialog for the cell and go to the Numbers tab. You should find the format code @ there.
If so: select any truely numeric format and push OK. Then edit the cell again (by appending a space to the formula e.g.) and confirm. Now the formula should be recognized.
BTW: "m³=1.65" is nonsense. m³ is a well defined SI unit - and it's not defined to have the value 1.65.

Lupp ( 2020-07-28 13:49:06 +0200 )edit

"m³=1.65" is intended to be text (in full form this would be "1.65€/m³" - It represents the cost per m³ of water supplied in € by calculating the cost of supply (197.45) less the fixed charge (55.41) (Abonn) explained in the question which is why CONCAT is used - to join textual items with the results of formulas... The question is less about the detail (which was provided as a prop to assist in the question) and more about the syntax required for a nested CONCAT including text items and derived numbers with ROUND function.

GrahamLees ( 2020-07-28 14:22:06 +0200 )edit

add a comment

3 Answers

Sort by » oldest newest most voted

answered 2020-07-28 20:39:56 +0200

GrahamLees
13 ●2 ●3 ●7

updated 2020-07-28 20:42:12 +0200

So it seems that nested CONCAT complicated by a series of ROUND functions is a bit of an animal and the best solution to the question after many attempts at it is to create two lines in the sheet and enter the required calculations on separate lines. Might seem a bit of a cop-out but it works!

The end result is a balanced view on the report.

image description

This was achieved (for the 2019 columns below the totals) with the following lines and copied through to the other years:
=IF(D13=0,0,(CONCAT(ROUND((D13-C13)/B13,2)," € / m³")))
=IF(B14=0,0,(CONCAT("soit ",(ROUND(ROUND(D13-C13,2)/ROUND(B13,2)/1000,5))," € / litre")))

Thanks for you considered comments in response to my question.

edit flag offensive delete link

Comments

Creating a clearer and better maintainable structure based on helper cells is often recommendable.
Also if you finally want to have the result collected in one cell, it's often better to calculate parts to be included elsewhere independently. The helpers may be in the same row apart of the print ranges or even in hidden columns.

Lupp ( 2020-07-28 21:54:44 +0200 )edit

add a comment

answered 2020-07-28 14:20:05 +0200

keme

3707 ●2 ●15 ●57

updated 2020-07-28 14:37:56 +0200

As Lupp commented, if you entered the formula with that leading = and it is displayed verbatim instead of producing a result, the reason is that you have a @ in the primary format code. This instructs Calc to take everything entered into that cell as verbatim textual input without further interpretation.

When you fix that format, you also need to add one trailing closing parenthesis, ), to conclude the outer CONCAT.

Calc allows up to four separate (semicolon separated) parts in the format specifier, 3 for numbers and one for text. To specify a particular rendering of textual results while avoiding "forced text", you can use the @ as placeholder for your result in the fourth section of the format.

edit flag offensive delete link

Comments

now have error 509 on the edited formula (after adding the closing parenthesis). Are there parts of the equation where semicolons should be inserted as opposed to commas?

GrahamLees ( 2020-07-28 14:55:52 +0200 )edit

If there was a problem with you argument ("Function") separator, the two separate formulas you claimed to have worked in the question would also have reported an error.
Anyway, your formula, having substituted the commas with semicolons, returned the result you expected for me. The closing parenthese was added automatically.
What's your locale? Find the settings under >Tools>Options>LibreOffice Calc>Formula>>Separators?
Please report.
Concerning some problems with localizattion you may consider to read https://ask.libreoffice.org/en/questi....

Lupp ( 2020-07-28 15:16:13 +0200 )edit

now have error 509 on the edited formula

Error 509 is "missing operator". Are you certain that you have the formula excactly as displayed? Are the ROUND and CONCAT entries all capitalised (signifying that they are recognized as function names). If you have configured for other language than English, the function names may be translated.

Are there parts of the equation where semicolons should be inserted as opposed to commas?

Semicolons will always work. Comma is equivalent to semicolon for your case here, as long as your setup uses dot as decimal separator.

See alsu Lupp's comment for the full picture.

keme ( 2020-07-28 15:24:08 +0200 )edit

(I was editing my previoius comment to make it a bit more precise concerning some details, but when I tried to save the edited version, the comment was already locked. That's not exactly important,but:)
The original question had in one place
(197.45-55.41)/86,2) (resulting in 1.65) and in a different place (197.45-55.41)/142.04,2) (resulting in 1) while the text suggested both sub-expressions should be the same. Of course, my test based on copy/paste returned the 1.

Lupp ( 2020-07-28 16:19:18 +0200 )edit

@Lupp My error, I copied the wrong line. I have edited the question to provide the correct line - thanks.

GrahamLees ( 2020-07-28 16:57:22 +0200 )edit

add a comment