+ Reply to Thread
Results 1 to 27 of 27

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

  1. #1
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

    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.
    Last edited by 6StringJazzer; 04-07-2022 at 09:17 AM. Reason: Better title, and moved from Excel General

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,487

    Re: Summing up numbers in a field

    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.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

    Re: Summing up numbers in a field

    Thank you, but is there no simpler way, as in not needing to write a function for it?

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,575

    Re: Summing up numbers in a field

    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
    Last edited by XLent; 04-07-2022 at 08:34 AM. Reason: edit: XL2003 rather than XL2007!

  5. #5
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

    Re: Summing up numbers in a field

    For the linebreak a simple space is enough?

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,575

    Re: Summing up numbers in a field

    not sure I follow -- attached is a working example of post #4
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

    Re: Summing up numbers in a field

    Btw, I'm using OpenOffice. Your example has
    in field B5: {=_Eval}
    in field C5: 1+3+5+7+11

    where is the "RefersTo: =Evaluate(C5)" statement?
    By default macros are not enabled, or?

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,575

    Re: Summing up numbers in a field

    we have posted Excel solutions as opposed to OpenOffice -- this thread should be moved to the appropriate forum - e,g https://www.excelforum.com/for-other...mobile-os-etc/

    I will flag to the mods to move the post

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,875

    Re: Put an arithmetic expression in one cell, and evaluate it in another

    I have moved the thread and changed the title to describe what you are actually trying to do.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,648

    Re: Put an arithmetic expression in one cell, and evaluate it in another

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  11. #11
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

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

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

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,648

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

    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.

  13. #13
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,875

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

    Quote Originally Posted by digerdiga View Post
    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)

  14. #14
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

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

    It is having a problem with
    EvalCell = Evaluate("=" & R.Value)
    and telling me:"Error. Objectvariable not specified."

  15. #15
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Trkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    945

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

    If you prefer using "Libre Office", this works;

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

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

    This looks awful. :-P
    I can not believe there is no version for OpenOffice.

  17. #17
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,875

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

    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/

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

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

    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 arent 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: copy to clipboard
    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.
    Dave

  19. #19
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

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

    Pffft!!!

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

  20. #20
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

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

    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
    Last edited by digerdiga; 04-07-2022 at 05:43 PM.

  21. #21
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

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

    =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: copy to clipboard
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

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

    Can you explain, why Virustotal flags your file as malware ? :-P
    It does not do so for the .xlsx though.

    And
    Please Login or Register  to view this content.
    returns 0.

  23. #23
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

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

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

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

  24. #24
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

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

    Yes, where do I manually set the ctrl+shift edit mode?

  25. #25
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

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

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

  26. #26
    Registered User
    Join Date
    04-07-2022
    Location
    Germany
    MS-Off Ver
    2003
    Posts
    11

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

    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 :-|
    Last edited by digerdiga; 04-07-2022 at 06:32 PM.

  27. #27
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] DAX Question: Summing a value field for each unique identifier field
    By DizerX in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-19-2022, 12:13 PM
  2. One Field Not Automatic Summing if Use Timeline (>1 month)
    By Jhon Mustofa in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-29-2021, 03:56 AM
  3. Summing of one field where multiple fields have to be similar
    By schmidt1962 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-13-2016, 10:33 AM
  4. Summing an averaged pivot field....
    By TMan80 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-09-2015, 07:29 AM
  5. Summing every second field in a row
    By nevrborn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-05-2007, 03:58 AM
  6. [SOLVED] PivotTable - summing more than one data field
    By Kruti in forum Excel General
    Replies: 4
    Last Post: 07-26-2006, 07:45 PM
  7. [SOLVED] Summing a Field while Using an Autofilter
    By Lost and Looking for Help in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2006, 01:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1