Results 1 to 9 of 9
Thread: formula in table (2002)

20050131, 21:39 #1
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 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,b15000))

20050131, 22:13 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 Posts
Re: formula in table (2002)
Thanks (again), Hans...but, why didn't the other formula work?

20050131, 22:14 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: formula in table (2002)
The calculation could look like this:
{ = { = 0.06*MAX( { =B15000} , 0) } + { = 0,065*MIN(B1 , 5000) } }
Remember, don't enter the field brackets yourself, use Ctrl+F9.

20050131, 22:22 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 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.

20050131, 22:37 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 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.

20050131, 22:42 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: formula in table (2002)
Check it carefully, I got incorrect results for some values.

20050201, 03:07 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 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?

20050201, 08:47 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: formula in table (2002)
Inserting an Excel table is certainly a lot less hassle than using Word formulas.
A Wordonly 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(Amount5000,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.

20050201, 13:36 #9
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 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.