Calculate Totals in InDesign Tables
Jack wrote:
Do you know a way how to create spreadsheets in InDesign? I want to set up an invoice form with a table that adds up certain cells when I punch in $ values, like in Exel.
Unfortunately, InDesign won’t do that out of the box. Fortunately, the SmartStyles plug-in from Woodwing will do it for you. Unfortuanately, I haven’t tried this feature in SmartStyles because I don’t have a recent version of it. Fortunately, other folks have reported to me that it works. Unfortunately, I don’t know the scope of its abilities to do calculations (only addition? subtraction? multiplication?). Fortunately, you can get a demo version or download the manual and probably learn if it’ll work for you pretty quickly. Unfortunately, I can’t do this for you because I’ve got in-laws coming for the holidays, we have 30 cm of snow (our area normally gets no more than about 5 cm), and my wife wants me to clear the driveway. With what, a soup ladle?
So. Do me a favor. Anyone who has used SmartStyles, let us know below how well it works for performing spreadsheet-like calculations inside InDesign.
By the way, SmartStyles does far more than just calculations, of course. It lets you sort data in a table, create sophisticated table styles (more sophisticated than you can in ID itself), and so on.
 |
Not (yet) in CS4!
They do have a beta everyone can download:
https://labs.woodwing.com/content/smart-styles-cs4-beta
This would be a great feature for future versions of ID, maybe everyone should request this on Adobe’s feature request forum.
https://www.adobe.com/cfusion/mmform/index.cfm?name=wishform
It should be fairly simple to create a script that adds such functionality. You would simply create a new conditional text condition named something like “formulas”, type your formulas inside your tables and assign them to that condition. Then the script would go through, parse the expressions, perform the calculations, and then write the results into the same cells as the formulas on a different condition named something like “calculation results”, replacing any values that might be there from a previous run.
That way, the original formulas would be preserved, making future updates as simple as re-running the script, but they would not show up in print because you can just hide the condition containing all the formulas.
The disadvantage is that numbers wouldn’t update live, you’d manually have to re-run the script to update the calculation results, but other than that I think it would be a very usable solution.
I’m sorry I don’t have enough time right now to write such a thing myself (besides I currently have no real use for spreadsheet features inside InDesign, and reordering rows/columns with the mouse would be more of a priority for me), but it shouldn’t be too hard to do for someone with scripting experience. The expression parsing would obviously be the greatest obstacle to overcome.
Hold on! I just received a note from the folks at ulrich-media that they have a freeware German script that does calculations. Sounds like they may be able to translate it for us English speakers. I’ll let you know here when I hear more about that.
I must protest, Sir: getting a native spread-sheet function in InDesign would be the ultimate feature-creep. The scripts and plugins will have to do for the Excel crowd. So Norway votes Nyet!
Jean-Claude pointed out via email that Dave Saunders had a simple script for doing addition in table cells: Just select some cells and run the script — it adds up the numbers in all the cells and places the result in the lower-right-most cell. It cannot deal with dollar signs or any other non-number character, but it still could be useful… Dave has kindly given us permission to post the script (zipped up) here for your downloading pleasure.
Well, the scripts are getting better and more robust… Harbs took Dave’s script and taught it to ignore any non-numerical information in the cells (like dollar signs, etc.)… You can download that version here. Thanks Harbs!
Actually, it not only ignores all non-numeric info, it actually copies the currency symbol from the first cell (if one exists) and inserts it before the number of the total.
Point, set, match to Harbs. Well played! :)
I live in Minneapolis, where it never occurred to me that a soup ladle might be any more successful than the Delete key to clear the deep snow that often covers our driveway.
David, you often innovate brilliant and non-obvious solutions to common problems, so I’m wondering if you’ve found a new ladle implementation?
What about doing calculation in forms for Indesign docs that are being converted to PDF like an downloadble order form?
Becca
Becca, you would set up the calculation fields in Acrobat, after you export it.
Here’s the online help page:
Acro 9 calculate form field tab
This is a handy script… If someone out there knows how to modify Harbs addition script and make it multiply cells I would really appreciate it!
Thanks!
Ken
@Ken: It’s really quite simple:
Chnage lin #15 to:
var sum = 1;change line #19 to:
sum = sum * ParseCleanFloat(cells[j].texts[0].contents);and change line #42 to:
return 1;Of course this will give you a starting value of 1 even if it should be zero… (but then you wouldn’t be multiplying values) ;)
Harbs, I’m sure I could figure this out eventually, but how would I change your script to subtract and divide?
Thank You!
Hello
A question to tables, perhaps not linked to the topic above.
If I have created a table with data, and I get some new data to add in to 2 new cells in the middle of the table. I don’t want to delete any old data, and I don’t want to add a complete new row in the middle.
Is it possible to push cell data forward to the next cell and further down to a new row. So it goes like a loop down. Or do I have to add a new row and move all the data manual.
It would be great to stand in one cell and push”tab” and the data highlighted jumped to the next cell. = the rest of the data in the following cells also jumped a cell forward.
I am not sure I am making my self clear, a bit hard to explain.
Best regards
Malin
@Malin: Unfortunately, the data in the cells are not linked, so you can’t force them to all move. But you can’t really just add 2 new cells in the middle of a table, either. A table is a rectangular grid. You can select a cell and Split it, though.
Nikki,
I have experimented a bit with Harbs’ script, adding a user defined formula in the mix. It works, insofar I can now specify (x + y) (current total plus current cell) and variants, but I’m having a hard time finding a use for subtract and divide.
Each cell only holds one number; what would be the result of subtracting all of them? (Most Likely Answer: the negative of adding all of them, so you can use the original script and type a minus in front of the result).
What would be the result of dividing them against each other? Given the list 1,2,3,4, and ignoring for the moment the first run (as there is nothing specific in ‘sum’ at the start), would you want to end up with 1/2/3/4? (= 1/24th)
Please help me.
I need to create table by importing text from msword. Providing even space between every column is a difficult task for me. Now what i do is
1. create a text frame (of same size as the page)
2. Import text
3. Apply required styles
4. Use C+S+T and set tabs manually, to view the columns legibly
5. Place a text/rectangular frames of width equal to the longest word to be set in each column. If there are 10 columns, then there will be 10 frames sized as per longest text in each column.
6. Then select the frames, in Align (C+,) window, distribute in evenly.
This process consumes lot of time. I used table menu, but still even spacing between column texts (table width should be equal to page width) is difficult.
Is there any script to achieve this? Any easy methods to save time? Please advice… Please.
@ramavarshny: It sounds like you may need a tutorial on how to make and adjust tables in InDesign. After you place the content, convert it to a table, then apply styles to the text in each column or row and adjust the widths of each column/row. If you need the table to reach the width of the page, then you can stretch it out, or just make the text frame the width of the page before converting the text to a table. For more information, see Real World InDesign, or the lynda.com Essential Training videos, etc.
Thanks Blatner. I ll surely check them out.
Great, thanks a lot Blatner. Now I am able to create tables in table mode easily and much faster than the old way. Spacing is accurate and table looks pretty neat too. Thanks a lot, may God bless you.
Hello, thankyou for sharing this script! It has some great functions and so excited by its use.
Any idea how I can change the script into three new script:
– Divide by 11 (calculate tax)
– Multiply by 0.3 (calculate deposit)
– Multiple by 0.7 (calculate remainder payment)
Figured out the multiply by 0.7 and 0.3. Now trying to work out how to subtract two cells from each other?
Excuse the bombardment – one more question! We have change script to calculate 10% tax by changing the code to the below which divides the total by 11 to calculate the included 10% tax.
row 15: var sum = 1;
row 19: sum = ParseCleanFloat(cells[j].texts[0].contents) / 11;
row 42: return 1;
Now the tricky part, this final cell which as the calculation we would love for it to come up with the value then the text “inc gst”. Any idea how this can be done?
And still trying to figure out the subtraction. Has us stumped?
Handy script, thanks Harbs!
“Calculs Raynaux” is the best script ever made for calculating inside InDesign tables: https://www.bcv-creation.com/calculs-dans-indesign/
It’s in French langage, but Google translate is your friend.
It also works with InDesign CC.
Hi there, great skript, Harbs!
Is it possible to change the script so it does the following:
1) decimals are separated by comma, not by point (1,5 instead of 1.5)
2) sums are displayed with a delimiter symbol and to the 2nd decimal place at all times (even in case of two zeros): 1.500,00 (one thousand five hundred and zero cents)
Thanks for everybody’s help!
Great script. Thanks for the heads up.
Hello,
Calculs Raynaux is now available in version 3.02 (with more features). Also in english and german.
Thanks to tell about !
ooops!
Calcul Raynaux is here : https://www.bcv-creation.com/calculs-dans-indesign/