MS Word problem with calculations in Table

  Nosmas 19:35 03 Apr 2006
Locked

I have set up in MS Word a main document and source document, which performs a mail merge to produce a Form Letter. The mail merge works OK and the appropriate Merge Fields are correctly inserted into the Form Letter. The main document is an advice to individuals and team leaders re competitions they wish to enter and includes a table, the first three columns of which are – Col A – Competition Name, Col B – Accepted and Col C Entry Fee. Row 1 contains the column headings and in Rows 2 to 12 the names of various competitions. In Row 13 cell C13 has the formula: -

{=Sum(C2:C12)\#”£#,##0.00;(£#,##0.00)”} and correctly shows the total of any amounts (entered manually) in Col C.

I am having problems in getting a conditional formula to work. In Col B an X will signify acceptance of an entry (in the competition for that row) and in Col C the formula is required to calculate the entry fee, which will vary according to the number of people in the team for that competition. I envisage something like: –

{=IF B2=X Product (2.90,2)}. With an appropriate number format (##,#0.00) inserted in the formula dialogue box this should produce 5.80 in cell C2 and likewise in other cells in Col C.

I know the ‘curly brackets’ have to be inserted by using Ctrl + F9 and not typed in from the keyboard. However all my attempts seem to produce a Syntax Error in cell C2. On one occasion I did manage to update C2 by using F9 but when I removed the X from B2 and did another update the 5.90 remained in C2 instead of a blank because there was no acceptance for that competition.

All suggestions for resolving this problem gratefully received.

  VoG II 21:09 03 Apr 2006

My limited understanding of functions within Word tables is that the results do not update when the table changes. Wouldn't it be easier to use Excel or the spreadsheet in the free Open Office?

  Nosmas 23:30 03 Apr 2006

Thanks for your reply. From what I have read, the updating doesn't occur automatically as in Excel, but requires the cell or the whole table to be selected and then pressing F9 will update the results, and this is what happened on one occasion.

This is such an elementary requirement for the particular job I am working on, that using Excel seems a bit over the top. If I can't get Word to work I may have to use Excel

  Simsy 08:43 04 Apr 2006

by default... It can be changed to "Manual Calculation", and when this is done F9 does indeed recalculate things...

The option is under Tools>Options>Calculation.

The setting is for Excel itself... that's to say it affect how Excel behaves, rather than how the particular .xls files behaves. (Unless some VBA is used.)

Hope this helps,

Regards,

Simsy

  Nosmas 09:16 04 Apr 2006

Thanks for your response. My reference to updating not occurring automatically was not re Excel itself but to the updating of cells in a Word Table.

Are there any Word/Table/CONDITIONAL FORMULA experts out there who can please tell me what is the correct syntax for the formula I proposed in the 4th para of my original post? Word Help is not much use in this respect.

  VoG II 10:04 04 Apr 2006

Try asking at click here

  Nosmas 17:30 04 Apr 2006

Many thanks for that very useful link which I have added to my Favourites. A search of the threads didn't throw up a solution, so I have posted my problem and hope some knowledgeable soul will provide me with an answer.

Continuing my experiments with the syntax, this formula: -

{IF {B2<>X,””,{=PRODUCT(2.9,2)\#”#,##0.00”}}} was accepted in C2 without any error message, but when the cell was selected and updated by F9 no result was displayed whether or not B2 had an X. I then tried inserting an = sign before the IF and was rewarded with: - !Unexpected end of formula

Any other experts on this forum please?

  Simsy 18:07 04 Apr 2006

I did, indeed, misread what you'd put... I think I missed the "as" completely!

Good luck,

Regards,

Simsy

  Nosmas 18:37 04 Apr 2006

No need to apologise, it is very easy to misread something.

Something else I have just found out. When I ran the mail merge, the formula for calculating the total of Col C came through in all the Sections of the Form Letter, and showed the correct amount (for manually entered values in the rows above) for each Section when that cell was updated. However the formula (shown in my post 04/04/06 @ 17:30) which produced NO RESULT nor an Error Message did not come through to the Form Letter!

  Nosmas 13:03 06 Apr 2006

Please can someone tell me what is wrong with the syntax in the following formula: -

{=IF(B3="X",PRODUCT(2.9,2),"")}

The ’curly brackets’ (braces) were inserted into the cell using Ctrl + F9. I constructed the formula in the cell originally from the keyboard and got a !Syntax Error, “ message. I then deleted the formula and constructed the formula in an Excel worksheet and it worked. I then copied the formula to the clipboard, went back to the (Word 2000) document table, inserted the braces into the cell and then pasted the formula between the braces and again got the same error message as before. Does anyone know the significance of the double quotes at the end of the message?

From experimenting it seems the problem is to do with the IF condition, because a straightforward function - PRODUCT(2.9,2) - with appropriate number formatting gives the correct result i.e. 5.80

To get the job finished I have had to manually update my Form Letter but would like to get this problem sorted so that I can automate the job next time I use it.

  squillary 17:08 06 Apr 2006

I need a 2-day warm-up to get into formulae and VB nowadays, so I can't help directly, but I can confirm that the way Excel and Word work are so completely different it's like they were produced by two different companies in two different countries in two different eras, each with no knowledge of the other's existence.

I started using computers 20 yars ago to solve just this kind of problem, integrating data from separate sources and integrating it in one fell swoop without further intervention. It never worked then, and by and large without a degree in true geekdom it doesn't work for me now either. Annoying, isn't it.

I usually end up breaking it down into the stages I can get to work and cobbling it together, Heath-Robinson style, in a process that takes about as long as if I did it with paper and Quill.

And they call that progress...

Apologies again for that whinge. I know I haven't helped. Best of luck in your efforts.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Lightwell software lets you create mobile apps without using code

Best value Mac: Which is the best £1249 Mac to buy

Comment désactiver les programmes qui s'exécutent au démarrage de Windows 10 ?