Put an arithmetic expression in one cell, and evaluate it in another [Open Office]

Hello,
I'm new and have a simple question:
In a field I want to write down the numbers that I add up for the reader to see, lets say (within field C5)

(1+3+5+7+11)

If I would put an equal sign infront of the bracket, this would evaluate to 27. This is what I yet don't want to do. In another field I want to add up these numbers. I thought this would easily work like this

=C5

But this does not work.

	B	C
2	(1+3+5+7+11)	27
3	(1+2)*2	6
4	(17-10)/2	3.5

	B	C
2	(1+3+5+7+11)	=CountItForMe(B2)

UDF code:

Please Login or Register  to view this content.

assuming XL2003

select the cell in which you want 27 to appear, then insert a name

Name: =_EVAL
RefersTo: =Evaluate(C5)

in the active cell enter =_EVAL and you will get 27

as this is an old XLM4 call you still need to enable macros, but there is no VBA code per se

I have moved the thread and changed the title to describe what you are actually trying to do.

It has been moved by someone - it is now in the Other Platforms section.

That's for you to find ut - let us know which do and which don't. Unless members happen to use OpenOffice themselves, they won't know.

Originally Posted by digerdiga

So are you sure your excel versions won't work for OpenOffice?

The only solution posted so far uses XLM4 which is an obsolete version of Excel macros that are still supported by Excel for backwards compatibility. I would be surprised if it works in OpenOffice but someone with specific OpenOffice knowledge will have to contribute.

I have seen similar questions before and have never seen a solution that doesn't require either XLM4 or VBA. OpenOffice supports VBA. This will work in Excel but I do not have OpenOffice to verify whether function Evaluate is supported:

Please Login or Register  to view this content.

To use:
Values as displayed

	A	B
1	5+6	11

Underlying formulas

	A	B
1	5+6	=EvalCell(A1)

You might try posting to the OpenOffice support forum. Normally I would not send you elsewhere but this is a specialized question.

https://forum.openoffice.org/

Not sure I understand the details of what you require.

Use this small table as a reference for the formula that follows.

	A
1	(1+3+5+7+11)
2	27

With the understanding that committing array formulas in OpenOffice works the same as Excel I suspect this will work.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. In cell A2

Formula:

Please Login or Register  to view this content.

Regional settings may require changing "," argument separators to ";". The attached file might correct that for you.

I would be interested to know.

Pffft!!!

I missed the 2003 bit. The format of this file is what you'll need.

Thanks for that. Do you mean I have to replace EVERY "," to ";" in case I fall in that category? Where do I look that up which of these is the separator?

Your file does not work. It contains just

(1+3+5+7+11)
Err:520

Btw: Virustotal flags this as malware :-O
MaxSecure Trojan.Malware.121218.susgen

=SUM(--TRIM(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(A1,"+",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"+",""))+1))-1)*LEN(A1)+1,LEN(A1)),")",""),"(","")))

In case that is too cumbersome I tried replacing them manually.

Formula:

Please Login or Register  to view this content.

No. I am afraid I can't explain the malware flag.

RE: returning 0
Did you try array entering the formula?

Either click in the formula bar or press the F2 function key in the upper keyboard register.

No, sorry does not work either. Still returns 0.
Btw your .xls file does not contain anything really. At least not what it is supposed to contain, but it is 55kb in size compared to 15kb for the .xlsx file :-|

I went back to my *.xls file and I got an error message that didn't show up earlier. The formula has too many levels of nesting.

I am very sorry, but I am completely out of ideas now.