locked
PRODUCT formular with cell references RRS feed

  • Question

  • Hi,

    in Word tables, one can also use cell references to calculate values

    e.g. "= A1 - A2" calculates the difference from values in cell A1 and A2

    but when I want to use this difference in the PRODUCT Formular I get a Syntax error

    e.g. "= PRODUCT(A1-A2;A3)"

    the minus sign seems to make Problems here.

    Can I somehow mask the difference (A1 - A2) so that the PRODUCT Formular accepts it ?

    Wednesday, August 13, 2014 7:33 AM

Answers

  • I'm not sure what you want here.

    If all you want to do is to replicate what the formula is doing, you could simply use a series of fields, like:
    {=A1}, {=A2}, {=A3}
    where the field braces are created via Ctrl-F9. These will return the 10, 20 and 5, respectively. So, if you use fields with text, like:
    ({=A1}+{=A2})/{=A3}
    when it's updated you'll see:
    (10+20)/5

    Conversely, if you input '(10+20)/5' into, say, A4, then a formula field like:
    {=A4}
    will return the result of the calculation, in this case 6.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Wednesday, August 13, 2014 11:21 AM
    • Marked as answer by George123345 Thursday, August 28, 2014 1:31 AM
    Wednesday, August 13, 2014 10:40 AM
  • You've got it !

    That's what I wanted

    Thanks

    • Marked as answer by Fritz Bilda Wednesday, August 13, 2014 11:23 AM
    Wednesday, August 13, 2014 11:00 AM

All replies

  • You could use:

    =PRODUCT(A1;A3)-PRODUCT(A2;A3)

    You could also use:

    =(A1-A2)*A3

    without the need for PRODUCT.

    To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
    http://windowssecrets.com/forums/showthread.php/154369-Microsoft-Word-Field-Maths-Tutorial
    or:
    http://www.gmayor.com/downloads.htm#Third_party


    Cheers
    Paul Edstein
    [MS MVP - Word]


    • Edited by macropodMVP Wednesday, August 13, 2014 8:56 AM
    • Proposed as answer by macropodMVP Wednesday, August 13, 2014 11:21 AM
    Wednesday, August 13, 2014 8:56 AM
  • Hi Paul,

    so every complex mathematical Expression can be described directly with the cell references and the basic operators

    Fine !

    Thanks for this hint

    Another question for me is, whether it is possible, to use cell references and generete a textual representation and Combine it to a complex string

    e.g.

    A1 = 10

    A2 = 20

    A3 = 5

    now I want to write in another cell the calculation Formular (A1+A2)/A3 with ist values

    e.g. "(10+20)/5"

    is this possible ?

    Wednesday, August 13, 2014 9:56 AM
  • I'm not sure what you want here.

    If all you want to do is to replicate what the formula is doing, you could simply use a series of fields, like:
    {=A1}, {=A2}, {=A3}
    where the field braces are created via Ctrl-F9. These will return the 10, 20 and 5, respectively. So, if you use fields with text, like:
    ({=A1}+{=A2})/{=A3}
    when it's updated you'll see:
    (10+20)/5

    Conversely, if you input '(10+20)/5' into, say, A4, then a formula field like:
    {=A4}
    will return the result of the calculation, in this case 6.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by macropodMVP Wednesday, August 13, 2014 11:21 AM
    • Marked as answer by George123345 Thursday, August 28, 2014 1:31 AM
    Wednesday, August 13, 2014 10:40 AM
  • You've got it !

    That's what I wanted

    Thanks

    • Marked as answer by Fritz Bilda Wednesday, August 13, 2014 11:23 AM
    Wednesday, August 13, 2014 11:00 AM