I would like to carry out some table Maths in Word 2003 but leave a blank cell if the result is not greater than zero. I have a reason for wanting to do this in a Word table rather than embedding an Excel worksheet.
By way of example, suppose I enter 60.00 in cell f5. I would like to calculate, say, 0.25 times this in cell g5 and display the result to two decimal places - this would give 15.00. I then would like to add these two cells together in cell h5, again displaying the result to two decimal places - this would give 75.00.
These calculations are relatively straightforward and can be done for example as follows:-
Cell g5 needs the formula
=ROUND(PRODUCT(0.25,f5),2)
and cell h5
=ROUND(SUM(LEFT),2)

However if nothing is entered into cell f5 (i.e. it is left blank) then cells g5 and h5 both show 0.0
I would like these two cells to also be blank in this case.

A sort of English come Word statement of this logic for cell g5 would be:-
IF (=ROUND(PRODUCT(0.25,f5),2) > 0) THEN (display (=ROUND(PRODUCT(0.25,f5),2)) ELSE (display the string " ")
And for cell h5 it would be:-
IF (=ROUND(SUM(LEFT),2)) > 0) THEN (display (=ROUND(SUM(LEFT),2)) ELSE (display the string " ")


So far, despite a lot of experimenting with the IF statement, I have not been able to achieve this. I suspect that using the IF function in Word might not be the right approach even if this would seem to be the logical method - perhaps it can be made to work with the right syntax - the Word Help examples don't seem to fit my needs.


So I am turning to this forum for ideas please.



Derek Shaw

Hello Derek,

I would have thoughtthe statement in H5 would be more like this:
=IF(ROUND(SUM(F5+G5),2) > 0,ROUND(SUM(F5+G5),2),"")

It's not quite VB in these statements, instead of if then and else, it's all done between commas:
=IF(condition, result if true, result if false)

__________________

Links: myFavoritesHomepage, Submit your URL to cyberJuice USA / UK online shopping links and blogs

Thanks for the suggestion. Unfortunately all this gives me is a Syntax error.
The Word Help indicates spaces as separators and not commas, also I get the impression that for the IF function the result should be text (i.e. True text and False text).

I have tried all sort of variations on your suggestion and the Word Help and all I get is ..... Syntax error!

My reason for wanting to use a table rather than an embedded Excel worksheet is that text entered into the form that I wish to create would often considerably increase the height of rows (actually it is to be an invoice blank and I would be using 0.175 and not 0.25 - for VAT) . A Word table would wrap to the next page. I would also put 17.5 into a cell and use this in the formulas. A simple edit of that one cell would enable the VAT rate to be changed if needed - instead of having to edit all the formulas in the blank table. But first I have to get this IF function working (or a suitable alternative) so as not to get a lot of cells with 0.0 where there was no entry for that row.

Derek Shaw

Any chance you could post what you done so far?

__________________

Links: myFavoritesHomepage, Submit your URL to cyberJuice USA / UK online shopping links and blogs

To madmonkey and anyone who can help.


I have discovered the cause of the Syntax Error. IF functions should not start with an = sign, they either start with IF or (IF depending on the content. However I have now not been able to create an IF function that shows the result of a calculation - only blank or sometimes text.


We (my wife and I) have been using an old version of WordPerfect (6.1 for Windows 3.1, from 1984) to produce printed invoices to send in the post. The tables in this old program leave the tables of Word 2003 behind. Microsoft's idea is that one should use an embedded Excel worksheet for this purpose. However, as far as I can see, an embedded worksheet cannot wrap to the next page if it gets too long to fit on one page but a table will.

We now want to send more invoices by e-mail and since Microsoft have persuaded everybody that Word is the best word processor no-one uses WordPerfect anymore. Hence I am trying to produce an invoice file in Word 2003 using a table with the very limited table functions that it has.

I have tidied up my basic design as follows:-
A basic table with enough rows to enter up to 10 jobs - containing all the formulas since it is not possible to insert rows and copy formulas down automatically updating cell references as is possible in Excel AND the tables in the old WordPerfect program.
The first row is a heading row
In cell g1 there is 17.5% to set the VAT rate
For each row, amounts without VAT are entered in cell f, VAT is calculated in cell g and the total in cell h.

Using row 5 as an example, the basic formulas that work are
=ROUND((g1*f5),2) in cell g5 and
=ROUND((f5+g5),2) in cell h5.
However this shows 0.0 in cells g and h if no entry is made in cell f (i.e. cell f is left blank for that row because there is no job entry in that row).

Since it is often convenient to create a new invoice from a previous one for that client (saving with a different name) and then edit it, especially since each also has another page with a fairly standard letter with that client's address etc., I want to avoid having to start from scratch each time and also deleting any unwanted rows.

This is why I am trying to use the IF function to control what shows.

Following the Word help, with the basic syntax of
IF Expression1 Operator Expression2 TrueText FalseText (using spaces as separators)
and using one of the more detailed examples as a guide I have tried the following (cell g5 given as an example):-

( IF ( = g1*f5) > 0 "( = ROUND((g1*f5),2))" )" "" )

I have also tried this without the outer brackets and also with additional brackets round the first g1*f5 and also with ROUND((g1*f5),2)) instead of the first g1*f5 in various combinations.
However all these this give a blank in cell g5 even if there is an entry in cell f5, except one of them, which I now do not know, showed the text of the formula instead of the calculated number!

Hope I have not bored everybody with all this but any help to solve the problem would be very much appreciated.

Derek Shaw.

No prob's Derek, thanks for that. A little confused as to why you haven't created the invoice in Excel alone? The invoice can be formatted in Excel just like it can in Word. The only reason I can think of is prehaps your doing mail merge or something like that that stops you from using Excel?

__________________

Links: myFavoritesHomepage, Submit your URL to cyberJuice USA / UK online shopping links and blogs

We have a graphic letterhead with logos (actually created with the DTP program PagePlus). Documents for sending by post are printed on this. For e-mail this has been captured as a bitmap and inserted into a Word document and formatted to be Behind Text. The invoice text can then be typed into this document so that the result looks exactly like those sent by post. The obvious approach therefore seemed to be to use a table. Clearly we have been spoilt by the better features of WordPerfect tables so it might be necessary to think again.

Is it possible to have a graphic image (i.e. our letterhead) behind an Excel worksheet (workbook) as can be done with a Word document?

Derek Shaw

Excel can certainly work with images in the same way as you can with Word. Not sure what you mean though by "behind and Excel worksheet", do you mean as a watermark?

__________________

Links: myFavoritesHomepage, Submit your URL to cyberJuice USA / UK online shopping links and blogs

The letterhead (for my wife's translation business) is A4 and comprises a frame running across the top (about 3.5 cm from the top and stopping about 8.5 cm from the right side). The frame turns with a quadrant (about 1 cm radius) and runs down the left side about 2.0 cm from the edge. It then turns with another quadrant across the bottom at about 1.5 cm from the bottom stopping about 3 cm from the right hand edge.

Across the top is a logo (created in a vector drawing program and inserted into the PagePlus DTP document) my wife' name with what she does and on the right address, telephone fax number and e-mail. The text extends about 1 cm below the frame which is why the frame stops at 8.5 cm from the edge. At the bottom the frame is interrupted for the VAT number and in he 3 cm space on the right is another logo for her professional membership (of ITI) - this logo was created as in the same way as the logo at the top.

It is most unlikely that any of the fonts used would be on anyone else's computer.

The PP document can be exported as a bitmap file which is A4 in size. This can be copied and pasted as a Metafile into Word. In order to type text in the Word file it is necessary to use Format picture, Layout to place the image Behind text.

I think this could not be inserted into an Excel file but maybe the watermark facility would work - I have not used this feature so far.

Derek Shaw