Syntax for nested CONCAT and ROUND functions in one cell
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)
What is the correct syntax to achieve this?
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."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.