Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    formula in table (2002)

    There's a new tax in my county. It's 6.5% of the first $5000 and 6% thereafter.
    I have Word (not Excel) document with a 1 row table and a formula in the table. I get a syntax error.
    This works for a formula: =.065*max(b1,5000)
    but, this gives a syntax error and I don't know why: =(.065*max(b1,5000))+(.06*max(0,b1-5000))

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    Re: formula in table (2002)

    Thanks (again), Hans...but, why didn't the other formula work?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: formula in table (2002)

    The calculation could look like this:

    { = { = 0.06*MAX( { =B1-5000} , 0) } + { = 0,065*MIN(B1 , 5000) } }

    Remember, don't enter the field brackets yourself, use Ctrl+F9.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: formula in table (2002)

    The formula I posted originally wasn't quite correct either, I have replaced it with one closer to your attempt. As you can see, it uses nested formulas to calculate the individual parts.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    Re: formula in table (2002)

    The first formula actually works better because the user enters $ 6,000.00 in the B1 cell...the second formula creates a syntax error because of all the decoration around the 6000.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: formula in table (2002)

    Check it carefully, I got incorrect results for some values.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    Re: formula in table (2002)

    My problem now is that I cannot get the tax calculation to format the answer as $ #,###.00 and, if the user enters $ 4,000.00 in the amount cell, the formula is an error. Do you think I should force them to use an inserted Excel 1 row sheet?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: formula in table (2002)

    Inserting an Excel table is certainly a lot less hassle than using Word formulas.

    A Word-only alternative is to use form fields in a protected section of the document (the section can be as small as a one line paragraph):
    - Display the Forms toolbar.
    - Put a text form field in the document; this is going to be the amount.
    - Double click the text form field.
    - Set the Type to Number.
    - Select a currency Number format.
    - Set the Bookmark name to Amount.
    - Tick the Calculate on exit text box.
    - Click OK.
    - Type some text, then put another text form field in the document; this is going to be the tax.
    - Double click the new text form field.
    - Set Type to Formula.
    - Enter the following in the Expression box:

    =0.065*Min(Amount,5000)+0.06*Max(Amount-5000,0)

    - Set a currency Number format.
    - Click OK.
    - Protect the document for forms, specifying only the section containing the text form fields to be protected.
    - When the user enters a value in the Amount form field and presses Tab, it will be formatted as currency automatically.
    - The tax will be calculated and formatted as currency too.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    Re: formula in table (2002)

    What a nightmare, don't you agree? I entered a 1 row Excel table and protected all of the cells other than the original amount and put the simplistic formula in to calculate the tax. SO much easier and logical. Too bad Microsoft doesn't allow you to write easy/typical/needed formulas and formatting in tables (like WP does, btw). Maybe they're saving that for a new release in 2004, or just want to push all calculation work toward Excel (which probably makes sense).

    Thanks for your continued efforts and head banging on this one.

Posting Permissions

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