Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
Lista Forumurilor Pe Tematici
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login

POZE MRRRR'S FORUM (VIEW ONLY)

Nu sunteti logat.
Nou pe simpatie:
Popa Bianca
Femeie
24 ani
Valcea
cauta Barbat
25 - 63 ani
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [WORD] Using Formulas in Word Tables (SUM, IF etc.) Moderat de TRaP, TonyTzu
Autor
Mesaj Pagini: 1
TRaP
Moderator

Inregistrat: acum 6 ani
Postari: 787
IMPORTANT: For this to work properly you must not have any merged cells in the range you want to sum. There can be merged cells above or below though.

Now, say you have a table with 12 rows of data that you must sum (12 months in a year) and some of them are blanks (or have a - symbol in them). The rows are located in the 3rd column.

1. Select the entire table by clicking on the top left corner

2. Go to Insert tab -> click on Bookmark

3. Name it Table1 and click the Add button

4. Click in the cell where you want to have the sum inside the table.

5. Go to table Layout tab (appears when you are in the table).

6. Click on Formula (located at the far right corner of the tab).

7. A box will appear with a =SUM(ABOVE) formula in the Formula field.

8. Instead of ABOVE write Table1 C2:C13 (mind the space between Table1 and C2:C13). The formula from no. 7 becomes: =SUM(Table1 C2:C13)

9. Click OK. The result should be the sum of all the numbers in the column, regardless of the table headers.

IMPORTANT: Ranges set in formulas in Word are written in the same manner you would write it in Excel, except here you will have to imagine the column letters and row numbers

IMPORTANT: To edit the range in a formula you already set, you will have to first show the formula inside the cell (instead of the result), so press SHIFT+F9 to show the formula, or right click the result and select Toggle field codes. Press SHIFT+F9 again to hide the formula and show the result instead.

IMPORTANT: Whenever you edit a formula as said in the above paragraph, make sure you don't accidentally delete any spaces or { }, just replace your range.

IMPORTANT: The total (SUM) won't automatically update when you change data in the column. You will have to select the result, right click it and select Update Fields.

NOTE: You can make formulas without using the bookmark naming of the table (Table1). But using Table1 helps doing formulas in other tables from values in Table1, otherwise you can't refer to the first table in a 2nd table.

NOTE: You can simply go to go to any cell in a table, to the Table Layout tab and click on Formula and then you can insert the formula like =A2+B2^2+C2-D2 and it will do the math.

For number formatting of the result, check the Source below.

Source:

More about formulas in Word:

Modificat de TRaP (acum 4 ani)


pus acum 4 ani
   
TRaP
Moderator

Inregistrat: acum 6 ani
Postari: 787
Made it also in VBA.

I need to add the sum for multiple tables in various files for the same range of cells (C3:C14) so the snippet below helps a lot.

If I need it on another range, after inserting it I can always press SHIFT+F9 to edit it, SHIFT+F9 again to hide, then Update field with right click on the sum.


Sub Table_Sum()

Dim i As Integer

Dim oTable As Table
If Selection.Information(wdWithInTable) Then
    Dim myTable As Range
    Set myTable = Selection.Tables(1).Range
Else
    MsgBox "You are not in a table cell, exiting Sub..."
    Exit Sub
End If

For i = 1 To 100

If ActiveDocument.Bookmarks.Exists("Table" & i) = False Then
    ActiveDocument.Bookmarks.Add Range:=myTable, Name:="Table" & i
    Selection.InsertFormula Formula:="=SUM(Table" & i & " C3:C14)", NumberFormat:=""
    Exit Sub ' exiting the For loop
End If

Next i

End Sub


If range differs (it usually does), it can also be added with an InputBox, so the snippet above becomes:

A few notes before posting the code:
- the code will work only if you are in a table cell, otherwise it will give you a message and exit the Sub
- range is set via input box and is written just like in Excel, eg. C2:C13
- then the code first checks if there is a table with the same name and if there is it goes to the next integer number
- you can also do SUMs horizontally, eg instead of C2:C13 you can add A2:E2
- you can even sum up across multiple rows and columns, eg. A1:J10
- you can add multiple bookmarks to the same table


Sub Table_Sum()

Dim i As Integer

Dim oTable As Table
If Selection.Information(wdWithInTable) Then
    Dim myTable As Range
    Set myTable = Selection.Tables(1).Range
Else
    MsgBox "You are not in a table cell, exiting Sub..."
    Exit Sub
End If

Dim rng As String
    rng = InputBox("Type the range which you want to sum (eg. C3:C14)")

For i = 1 To 100

If ActiveDocument.Bookmarks.Exists("Table" & i) = False Then
    ActiveDocument.Bookmarks.Add Range:=myTable, Name:="Table" & i
    Selection.InsertFormula Formula:="=SUM(Table" & i & " " & rng & ")", NumberFormat:=""
    Exit Sub ' exiting the For loop
End If

Next i

End Sub


As stated above, SUM results don't automatically update the result upon entering new data into the range, so you will have to do this manually or you can always use another button linked to the VBA code below which updates all fields in the document:

Modificat de TRaP (acum 4 ani)


pus acum 4 ani
   
Mrrrr
AdMiN

Inregistrat: acum 17 ani
Postari: 2228
More info / workarounds

I tried to use the { =SUM(ABOVE) } formula in a table but the answer was obviously incorrect

Better than an Easter egg: Using ToolsCalculate instead of using a formula

Create and use formulas in tables in Word

Description of Word formulas and how to use them

More on cell references in Word


_______________________________________


pus acum 4 ani
   
Mrrrr
AdMiN

Inregistrat: acum 17 ani
Postari: 2228


Code:

Word functions and their descriptions:

Function     Description
ABS()        Calculates the absolute value of the value inside the parentheses.
AND()        Evaluates whether the arguments inside the parentheses are all TRUE.
AVERAGE()    Calculates the average of the elements identified inside the parentheses.
COUNT()        Calculates the number of elements identified inside the parentheses.
DEFINED()     Evaluates whether the argument inside parentheses is defined. Returns 1 if the argument has been defined and evaluates without error, 0 if the argument has not been defined or returns an error.
FALSE         Always returns 0
IF()        Evaluates the first argument. Returns the second argument if the first argument is true; returns the third argument if the first argument is false. Note: Requires exactly three arguments.
INT()         Rounds the value inside the parentheses down to the nearest integer.
MAX()         Returns the maximum value of the items identified inside the parentheses.
MIN()         Returns the minimum value of the items identified inside the parentheses.
MOD()         Takes two arguments (must be numbers or evaluate to numbers). Returns the remainder after the second argument is divided by the first. If the remainder is 0 (zero), returns 0.0.
NOT         Evaluates whether the argument is true. Returns 0 if the argument is true, 1 if the argument is false. Mostly used inside an IF formula.
OR()        Takes two arguments. If both are false, returns 0, else returns 1. Mostly used inside an IF formula.
PRODUCT()     Calculates the product of items identified inside the parentheses.
ROUND()        Rounds the first argument to the number of digits specified by the second argument. If the second argument is greater than zero (0), first argument is rounded down to the specified number of digits. 
                If second argument is zero (0), first argument is rounded down to the nearest integer. If second argument is negative, first argument is rounded down to the left of the decimal.
SIGN()        Takes one argument that must either be a number or evaluate to a number. Evaluates whether the item identified inside the parentheses if greater than, equal to, or less than zero (0). 
                Returns 1 if greater than zero, 0 if zero, -1 if less than zero.
SUM()        Calculates the sum of items identified inside the parentheses.
TRUE        Always returns 1.

The arguments can be:

Argument     Description
ABOVE        Cells above the current. For example, =SUM(ABOVE)
BELOW        Cells below the current. For example, =SUM(BELOW)
LEFT         Cells on left of the current. For example, =SUM(LEFT)
RIGHT        Cells on right of the current. For example, =SUM(RIGHT)



_______________________________________


pus acum 4 ani
   
TRaP
Moderator

Inregistrat: acum 6 ani
Postari: 787
You can't do much math in Word tables, but you can do some things.
You can do math outside of tables too, but you must bookmark tables to do that, so the formula knows which table you are referring to.

A table in Word behaves similar to cells in Excel, meaning that if you have a 10 by 10 table, you can refer to the cells in the table as A1, B3, C10 etc. and you can use these notations in formulas, eg. =A1+B1-(C1*D1)+E1^2+D1/5

It's harder to make formulas in Word and sometimes a bit tricky. You can use IF but can't put condition IF(A1=""...) because word cells are never empty, they contain an end-of-cell character. You can put condition IF(A1=0...) but if you have 0 in your cells and want to refer to blank instead of zero, this can become tricky - I haven't got a solution for this, but see topic below with my question about this matter on another forum:

In order to set numbering formats for SUM in word, or for any calculations, you can add the number format in the end of the formula, eg. for showing no decimals you can use:
=SUM(ABOVE) \# "0")


pus acum 4 ani
   
Pagini: 1  

Mergi la