Results 1 to 15 of 16
Thread: Help with a form (2003)

20080909, 06:43 #1
 Join Date
 Sep 2008
 Posts
 12
 Thanks
 0
 Thanked 0 Times in 0 Posts
Help with a form (2003)
Hiya all,
Please see my 2 examples. The problem comes with part at the bottom. I need the totals at the bottom. 9 out of 10 times the result will be total 1. If the result is total 1 (which is the case if the totals are not negative). if they are possitive i need the total 2 to show 0.00.
like wise if the total is negative i need it to show 0.00 (or blank) and the correct result in total 2.
i hope this makes sense?
Emma x

20080909, 15:17 #2
 Join Date
 Feb 2001
 Location
 Silicon Valley, USA
 Posts
 23,112
 Thanks
 5
 Thanked 93 Times in 89 Posts
Re: Help with a form (2003)
You can do this by wrapping an IF field around your equation. Please see the attached example.

20080909, 20:58 #3
 Join Date
 Sep 2008
 Posts
 12
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with a form (2003)
This works a treat!
Thank you :)

20080909, 21:25 #4
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 Posts
Re: Help with a form (2003)
Hi Emma,
Here's an even simpler way:
Change your picture switch to # "£'0.00';(£#,##0.00)"Cheers,
Paul Edstein
[MS MVP  Word]

20080910, 06:24 #5
 Join Date
 Sep 2008
 Posts
 12
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with a form (2003)
Please could someone message mw about this when they are online.
Thanks.

20080910, 07:41 #6
 Join Date
 Feb 2003
 Location
 Pacific Grove, California, USA
 Posts
 717
 Thanks
 11
 Thanked 34 Times in 28 Posts
Re: Help with a form (2003)
Hi Emma,
What is your question? You seemed to have it handled...
Kim <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

20080910, 07:50 #7
 Join Date
 Sep 2008
 Posts
 12
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with a form (2003)
Well the original attachment i posted has 3 colums in it. 2 free ones and 1 that was a total. When the answer was given to me it worked for one column only.
Please see the other attachment that ive added now.
Also this menas the goal posts has changed.
The decision which calc to use at the botton depends on the two toals and the results. if calc 1 two options is a possitive result then i need to use calc 1 and the calc 2 should read zero throughout, but if calc 1 is a total negative then calc 1 should read zero and then calc 2 should give the correct outcome.
I hope this makes sense.
Emma x

20080910, 08:38 #8
 Join Date
 Feb 2001
 Location
 Silicon Valley, USA
 Posts
 23,112
 Thanks
 5
 Thanked 93 Times in 89 Posts
Re: Help with a form (2003)
You can create an IF field for your first column using the same approach. Copy the formulas from the sample document, press Alt+F9 to see everything, and replace the interior = fields with the correct formulas.

20080910, 09:06 #9
 Join Date
 Sep 2008
 Posts
 12
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with a form (2003)
ive tried this (loads of times) but must be missing something.
Im really good with normal formuals but just cant get this and its getting me mad!
Any help is welcome

20080910, 11:03 #10
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 Posts
Re: Help with a form (2003)
Hi Emma,
As I understand your question, you require certain cells to only show a nonzero value if the total is less than 0. Is that correct?
Which of the totals in:
. rows 9 & 10 (the ones you've titled 'Total overpayment Calc 1 = (d+e+f+g)  c' and 'Total overpayment Calc 2 = (b+c)  a')?
. column D (the one you've titled 'Total of 1 and 2')
do you require this treatment to apply to?Cheers,
Paul Edstein
[MS MVP  Word]

20080910, 11:26 #11
 Join Date
 Sep 2008
 Posts
 12
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with a form (2003)
For example:
If total 1 totals (+)£34.99 and total 2 totals ()£35.00 in calc 1, the total would be ()£0.01. In this secnario, i would require the Calc 1 to read £0.00 all the way across and the correct figues to show in calc 2.
If total 1 totals (+)£34.99 and total 2 totals (+)£5.01 in calc 1, the total would be (=)£35.00. In this secnario, i would reuire calc 1 to show the correct figuers ((+)£34.99(+)5.01(+)£35.00) and calc 2 to show £0.00 all the way across.
If total 1 totals ()£34.99 and total 2 totals ()£5.01 in calc 1, the total would be ()£35.00. In this secnario, i would require the Calc 1 to read £0.00 all the way across and the correct figures to show im calc 2.
Only the totals of the totals count...if that makes sense?
Thanks

20080910, 13:50 #12
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 Posts
Re: Help with a form (2003)
Hi Emma,
I'm sorry, but I don't find your explanation at all clear. It seems to me that you're saying all the values for Calc1 should display 0 if the sum of 'Total1' and 'Total2' on that row are less than 0, but I'm not sure if that's what you mean. Plus, I don't see how a value here would affect the values for Calc2  the only common figure between them seems to be your item c.
Perhaps you could post a table with some sample data producing the results you want modified and insert comments (using InsertComment) in the cells that you want to modify to say what they should be displaying.Cheers,
Paul Edstein
[MS MVP  Word]

20080910, 17:20 #13
 Join Date
 Feb 2001
 Location
 Silicon Valley, USA
 Posts
 23,112
 Thanks
 5
 Thanked 93 Times in 89 Posts
Re: Help with a form (2003)
I think you need to expand the "if" expression to sum across both columns. Currently, you have something like this in column 1:
<code>IF calc_of_column_1 >= 0 </code>
and you need something like this:
<code>IF (calc_of_column_1 + calc_of_column_2) >= 0 </code>
Does that work?

20080911, 08:46 #14
 Join Date
 Sep 2008
 Posts
 12
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help with a form (2003)
Ive attached a more clearer example (hopefully)
Thanks.

20080911, 11:40 #15
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 Posts
Re: Help with a form (2003)
Hi Emma,
Try the attached.Cheers,
Paul Edstein
[MS MVP  Word]