Help with a form (2003)

[emma2008]

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

[jscher2000]

You can do this by wrapping an IF field around your equation. Please see the attached example.

[emma2008]

This works a treat!

Thank you :-)

[macropod]

Hi Emma,

Here's an even simpler way:
Change your picture switch to # "£'0.00';(£#,##0.00)"

[emma2008]

Please could someone message mw about this when they are online.

Thanks.

[kmurdock]

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>

[emma2008]

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

[jscher2000]

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.

[emma2008]

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

[macropod]

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?

[emma2008]

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

[macropod]

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.

[jscher2000]

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?

[emma2008]

Ive attached a more clearer example (hopefully)

Thanks.

[macropod]

Hi Emma,

Try the attached.