Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Simple Word formula- can't get it to work

    I have a template used for generating invoices, and in the interest of making it work a little faster I was looking for ways to automate/speed up the process. The template already (using mailmerge etc) pre-populates the date, invoice number and customer information. I use drop downs for payment and shipping methods.

    I wanted to set it up to automatically do line item and total calculations. Discovered that formulas in Word do not automatically update, one has to either select the entire document and hit F9 or just go to the fields and hit F9. Not as quick, but still an improvement over what I had.

    Problem is the extensions for each line. I can't get them to work.

    Here is the formula I entered- IF(a2 > 0 a2*c2 " ") (that is how it is entered after using the Layout>Data>Formula menus)

    I want the item total (d2) to be a blank space, and only show a result if there is a quantity (a2), which then will extend out the total by multiplying it with the price (c2).

    I do not get syntax errors or anything, but when I actually create a new invoice from the template and enter quantities and prices I can't get the formula to update. Nothing happens, it stays blank.

    What am I doing wrong?

    Thanks,

    Mark

  2. #2
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Well, I thought this was an active board.

    I have learned more on my own, I figured I have to use nesting formulas- eg

    { if a2 > 0 { a2*c2 } " " }

    Problem is the result shows as 0 if there is nothing in a2 or c2. I want it to be blank.

    Anyone? Ferris Bueller?

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by madkiwi View Post
    Well, I thought this was an active board.
    A little patience on your part wouldn't go astray. Some of us have lives too, you know. Gee, some of us even like to sleep at night or have a meal or ...

    You would also do yourself a favour if you downloaded & read my Microsoft Word Field Maths Tutorial, a STICKY thread at the top of this forum:
    http://windowssecrets.com/forums/sho...Maths-Tutorial
    You'll find all the answers you need there.
    Last edited by macropod; 2014-07-16 at 00:20.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yeah, I must be stupid because I can't follow your guide. I had downloaded it 3 days ago actually, and it did not help.

    Thanks.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The tutorial shows multiple ways you could achieve your desired result. Possibilities include:
    • {=A2*C2 \# 0;0;}
    • {=IF(A2>0,A2*C2,0) \# 0;0; }
    • {IF {=A2}> 0 {=A2*C2 \# 0 }}
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Cross-posted at: http://www.msofficeforums.com/word-t...ula-table.html
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    The tutorial shows multiple ways you could achieve your desired result. Possibilities include:
    • {=A2*C2 \# 0;0;}
    • {=IF(A2>0,A2*C2,0) \# 0;0; }
    • {IF {=A2}> 0 {=A2*C2 \# 0 }}
    That gives me a 0 if there is nothing in A2. Which is what I already have. I wanted it to be blank if there is nothing in A2.

    Did not realize that posting on another forum was the definition of "cross-posting".

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by madkiwi View Post
    That gives me a 0 if there is nothing in A2. Which is what I already have. I wanted it to be blank if there is nothing in A2
    Garbage! Each of those formulae give blanks if A2 contains 0, is empty, or does not contain a number.
    Did not realize that posting on another forum was the definition of "cross-posting".
    Now you know. I'm not sure what you thought cross-posting was, if not that.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    Garbage! Each of those formulae give blanks if A2 contains 0, is empty, or does not contain a number.
    Well, your formulas all work, except they don't show decimals, which would be required when extending pricing. But that's ok, I don't want to bother you any more.

    Now you know. I'm not sure what you thought cross-posting was, if not that.
    I always thought cross posting was obnoxious repetition of the same thread on multiple sections of the same site, and I never have done that. Posting on different forums may be the dictionary definition of "cross-posting", but does not have the same connotations. Just because you are a reader on both sites does not mean everyone else is. I was trying to fine ANYONE who could help and I won't make that mistake again.
    Last edited by madkiwi; 2014-07-16 at 11:16.

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by madkiwi View Post
    Well, your formulas all work, except they don't show decimals, which would be required when extending pricing.
    Perhaps you should read more of the tutorial, concerning results formatting, or even my Word Mailmerge Tips & Tricks page (http://windowssecrets.com/forums/sho...ips-amp-Tricks), which has extensive discussion on these issues.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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