Ask Your Question

How to count number of operation?

asked 2018-08-09 10:22:02 +0200

Sergent gravatar image

What is the formule to count the number of operation in Calc ? like in one cell i write this "=2+52+662+71" And need the answer in other cell "4". Thanks so much for your help

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2018-08-09 12:25:36 +0200

Lupp gravatar image

updated 2018-08-09 13:13:37 +0200

Quoting @Sergent:
"What is the formula to count the number of operations in Calc? Like in one cell I write this "=2+52+662+71" And need the answer in other cell "4". ..."

The supposed result "4" for your example does not count the number of operations but the numbert of operands. The calculation explicitly is demanding 3 operations. How Calc does it in the background, and how many operations are needed then we don't know..

What result would you expect if the formula was =IF(A1+A2<100;2+52*662+71;0)?

To get a formula contained in a cell as a text result you have the function FORMULA() in Calc. (I didn't check for its French name. Suppose it's TEXTEFORMULE.)

To count how many cells in a range, say A1:A6, contain a formula you can use
{=COUNTIF(NOT(ISERROR(FORMULA(A1:A6)));TRUE())} entered for array evaluation with Ctrl+Shift+Enter.
The much simpler {=COUNTIF(ISFORMULA(A1:A6);TRUE())} or =SUMPRODUCT(ISFORMULA(A1:A6)) should also work, but there is a bug. A fix was recently announced. It shall be available in V 6.1.1.

For accumulating functions used on simple ranges like SUM(A1:C6) you can use
=COLUMNS(A1:C6)*ROWS(A1:C6)-SUMPRODUCT(NOT(ISNUMBER(A1:C6))) in an additional cell to get the number of (actual = numeric) operands. The overall size of the range is simply =COLUMNS(A1:C6)*ROWS(A1:C6).

Back to the simple continued sums like in the original example:
For =1+2+3+4+A4+99 in cell S1 =LEN(S1)-LEN(SUBSTITUTE(S1;"+";"")) would return the number of adding operands ("+") =5 and an additional +1 appended to the formula would produce the 6 as the number of operands (addends) including single references. To do similar things for expressions containing different operands and/or parentheses or... it's much more complicated.

Sorry! The last formula assumed S1 to contain the formula as a text.
S1 being the cell where the adding formula was entered the operand-counting formula must be:

edit flag offensive delete link more


Thanks a lot for this complet answer !! The last one is exactly what i need :
=LEN(S1)-LEN(SUBSTITUTE(S1;"+";"")) ... unfortunately in my case it return : #NAME? Any suggestion ?
(Format rectified by @Lupp .)

Sergent gravatar imageSergent ( 2018-08-09 12:48:32 +0200 )edit

Set your UI language to some English, or at least set
Tools>Options>LibreOffice Calc>Formula>Use English function names enabled.
The other option is to guess in a clever way, probably searching through the list of availebla functions in the formula wizard.
(To use common terms is a basic requirement for the usefulness of international forums. Don't be stubborn concerning your local language... I'm a German living in Germany. So is @ebot .)

Lupp gravatar imageLupp ( 2018-08-09 13:02:35 +0200 )edit

Woaw ! It works now using your formula : =LEN(FORMULA(S1))-LEN(SUBSTITUTE(FORMULA(S1);"+";"")). Thanks a loooot !! I needed to add +1 to the formula to get the correct number i wanted. Now i'm in front of a new issue : I've some cell with just one number (without +) so it display #N/A and ruined my total... Any idea how to consider this #N/A as 0 ?

Sergent gravatar imageSergent ( 2018-08-09 16:25:31 +0200 )edit

Just a hint: To find the cause of a problem and subsequently a solution yourself now and then is more fun.
The formula I suggested uses the Calc function FORMULA(). It cannot work on Cell content that is not a formula at all. If there is a single number, say 13, but made up to be returned as a formula result by entering =13 the #N/A will vanish.
If you want to also analyse expressions given as texts in the place of formulae lead by the "=", things get a bit more complicated again.

Lupp gravatar imageLupp ( 2018-08-09 17:48:26 +0200 )edit

Thanks you so much for your assistance et rapidity ! Thank you, Merci !!! I've to make the topic as "Solved" if i find how..!

Sergent gravatar imageSergent ( 2018-08-09 18:44:56 +0200 )edit

answered 2018-08-09 11:08:52 +0200

Sergent gravatar image

Hello ! Danke for the fast answer ! I just dont be enough clear. What i need is the number of "calcul". For example : 1+2+3+4+5+6 = 6 or 7+8 = 2 or 55+21+41+18=4

(I've search on internet, and the closest answer was trough Excel with the extension TEXTEFORMULE of MoreFunC)

edit flag offensive delete link more


Oh sorry, i misunderstood. There is no corresponding function in Calc itself. I did not find it. Have you already looked at the existing extensions?

ebot gravatar imageebot ( 2018-08-09 12:02:26 +0200 )edit

answered 2018-08-09 10:46:05 +0200

ebot gravatar image

You can do cell linking, e.g. "= B2" work. image description

Or you make a simple summation. Set the cursor e.g. in cell B10 and click the sums icon. See attached file.

C:\fakepath\Lösung für Summe.ods

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-08-09 10:22:02 +0200

Seen: 31 times

Last updated: Aug 09