Calculation function in table won't add correctly

[TheContractorsGroup]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hey All! =)

Happy middle of the week!

I have created a table that has a column of numbers on the right.

I have used Form Fields for entering the numbers.

There are several sections and each section several rows.

Each section adds up so if I have a section that has 3 rows and I enter 10.00 in each form field then that section will total up to 30.00.

The next section has 5 rows and I enter 10.00 in each form field and that section totals up to 50.00.

I have several sections like this and each section totals up perfectly.

But when I get to the bottom and need a Grand Total, I can't get it to add correctly.

Here's an example:

Form Field Name Amount entered in Form Field

Text1 10
    Text2 10
    Text3 10
    Text4 30 (this is an auto sum, Text1 thru Text3)

Text5 10
    Text6 10
    Text7 10
    Text8 10
    Text9 10
    Text10 50 (this is an auto sum, Text5 thru Text9)

Now I want to add the form fields Text4 and Text10 together, and the total will appear in the form field Text11.

Logically the sum should be 80 but instead it's 8080.

What am I doing wrong?

Thank you so much for your help!!
Diane

 

[AlanMac]

What you can do is use a formula like:

{=SUM(B4,B9)} where B4 is the 2nd column 4th row of your table and where B4 is the 2nd column 9th row of your table. I am assuming that B4 and B9 are the table cells that hold your autosums.

Note the , comma between the cells as you only want to add the totals not the lot twice.

That should work, btw use F9 toi update calcs and Shift+9 to show formulae.

 

[Peter Jamieson]

You aren't doing anything wrong, but the product does not work with
intermediate values in form fields correctly - you may find the
following link useful:

http://support.microsoft.com/kb/211253

Peter Jamieson

http://tips.pjmsn.me.uk

 

[TheContractorsGroup]

Hi Guys!

Thank you for your help!

I'm trying to make any of the suggestions above work but I'm wondering if I need to know what the Table Cell Reference Numbers are. If I do need to know that, how do I find out what the Reference Number for a Table Cell is?

Thank you!!
Diane

 

[Peter Jamieson]

Broadly speaking, for a field inside a table, the references are to
cells with columns A,B,C and rows 1,2,3, i.e. the 4th row of the 5th
column is E4.

So if you have value 3 in B2 and value 5 in E4, then the field

{ =B2+E4 } in (say) cell F5 should evaluate to 8.

If the same field is outside the table, you need to bookmark the table
(e.g. select the table and create a bookmark like tbl1) then use, e.g.
{ =sum(tbl1 b2,tbl1 e4) }

a. Don't bookmark your table with a name that can be confused with a
cell reference such as T1
b. I don't think you can do {=tbl1 b2+tbl1 e4 }

Peter Jamieson

http://tips.pjmsn.me.uk