Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    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
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    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.
    Attached Files Attached Files

  3. #3
    New Lounger
    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 :-)

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 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]

  5. #5
    New Lounger
    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.

  6. #6
    5 Star Lounger kmurdock's Avatar
    Join Date
    Feb 2003
    Location
    Pacific Grove, California, USA
    Posts
    716
    Thanks
    10
    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>

  7. #7
    New Lounger
    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
    Attached Files Attached Files

  8. #8
    Super Moderator jscher2000's Avatar
    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.

  9. #9
    New Lounger
    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

  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

    Re: Help with a form (2003)

    Hi Emma,

    As I understand your question, you require certain cells to only show a non-zero 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]

  11. #11
    New Lounger
    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

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 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 Insert|Comment) in the cells that you want to modify to say what they should be displaying.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  13. #13
    Super Moderator jscher2000's Avatar
    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?

  14. #14
    New Lounger
    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.
    Attached Files Attached Files

  15. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Help with a form (2003)

    Hi Emma,

    Try the attached.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 2 12 LastLast

Posting Permissions

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