Hi, i want to ask how to count a cell with multiple addition like
sample: in cell A1 fx”=1+2”
it should count as 2 transaction
otherwise if i add more + , in counts
ty in adv
@EarnestAl changed tag to Calc
Sorry, I don’t understand the question, AFAIK Draw (tagged draw) doesn’t calculate in tables and you can’t add a Math object inside the table.
Maybe you could add a small sample document that shows what you are trying to do.
Remember too, English is not the first language of many contributors here so please always write out words in full
1 Like
Something like:
=LEN(FORMULA(A1))-LEN(SUBSTITUTE(FORMULA(A1);"+";""))
or with regular expressions
=LEN(REGEX(FORMULA(A1);"[^[\+]]+"))
1 Like
If you are using only “+” (not - / * ) then @mariosv first formula should work but I think you need to add a +1 to the end of the formula to allow for the first number.
1 Like
Thank you all for the help. God Bless
Suggested solution
For simple addition/subtraction of positive numbers, this formula (elaborating on @mariosv’s ideas) should work:
=IF(ISNUMBER(A1);IF(ISFORMULA(A1);1+LEN(REGEX(FORMULA(A1);"[^\+|\-]";"";"g"));1);0)
Strategy, background theory
- Assume that all +/- are binary operators (not unary)
- Is the cell content a numeric value?
- If so, is it a formula result?
- if so, count operators and add 1 (there will be 1 more operand than operator)
- If numeric but not formula, it is a simple number entry, so return the count of 1.
- Not numeric, so there is nothing to extract transactions from yet, so return 0
- If so, is it a formula result?
The core of the formula simply removes everything except + and - operators from the formula string, counts the operators (=string length) and adds 1.
pitfall
Note that the given formula fails if your “transaction pack” formulas use “unary +/-” to specify the sign of elements.
A formula like =-2+7+-1 is not correctly analysed.
You’d require a more elaborate formula to cater for that possibility.
1 Like
We can perform detailed analysis using the XFormulaParser interface.
If the cell contains a formula, then the getTokens method “returns the formula as sequence of tokens.”
2 Likes