Calculate percentages in Excel, select data, and merge into Word?

[jish]

Hi, I'm not asking anyone to figure this out, but just wanting to know if it's possible so I can suggest it to someone! I would like to know if one has a table of items and amounts, with the amounts in columns by year, along with the percentage change (say, from 2020 to 2021), like so...




2019
2020
2021



Item 1
amount
amount
amount
percentage change


Item 2
amount
amount
amount
percentage change


Item 3
amount
amount
amount
percentage change


Item 4
amount
amount
amount
percentage change



Also, one has some boilerplate MS word text that says something like the following: "The amount of Item 1 produced in 2021 increased by X% compared with that of 2020."

I would like Excel to determine what amounts increased by more than 10% and then somehow magically produce text that says "The amount of Item 1 produced in 2021 increased by X% compared with that of 2020; Item 2, XX%: and Item 3, XX%." It would have to know to insert the correct number of items, and the percentage increases should be in decreasing order in the text. If necessary, there could be boilerplate text in the Word document that could allow for 10 or more item increases and then the user could manually delete the extraneous blank items if only 6 increased by more than 10%, but that would be less helpful. Finally, if it would be easier for the text to be created in Excel and then transferred to Word, that would be fine also.

What I'm most interested in is the automated nature of this task and the resulting accuracy. Does it seem that this would be possible to do with a VBA script or is some other sort of software needed? Or is it even possible at all?

Sorry for the length of this question, and thanks in advance for advice!

[Dave]

Hi jish. I see U haven't had much luck with this. Yes it's very doable and XL is only as accurate as U tell it to be. If U provide a sheet and table name and the cell addresses of the data, I'm guessing someone would be able to VBA U something up. I would suggest just creating a Word document on the fly rather than using a template being that the amount of data transfer is variable. If there is else U want in the Word doc other than the ordered statements "The amount of Item 1 produced in 2021 increased by X% compared with that of 2020." is also needed along with what the resulting Word doc file would be. Good luck. HTH. Dave

[jish]

Hi, Dave---

Thanks very much for your thoughtful reply.

The “automated” paragraph I would be trying to create is part of a writeup that summarizes information in an accompanying set of tables—there are hundreds of writeups about hundreds of sets of tables, all with unique names. I'm a lazy editor and would like to suggest to the authors that they could have the software transfer information directly from a selected spreadsheet into a Word document automatically to ensure the accuracy of the information and keep me from having to cross-check each percentage manually…ugh.). It sounds as though it’s reasonable to at least mention it to the authors as something to explore, although the macro would also need to figure out which information to extract (any change greater than 10%).

Thanks again for taking the time to reply! I’m very grateful.

[macropod]

Assuming you have the table in Word, such math and cross-referencing are well within Word's capabilities. To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, at:
https://www.msofficeforums.com/word/38720-microsoft-word-field-maths-tutorial.html
or:
http://www.gmayor.com/downloads.htm#Third_party

Otherwise, for a table in Excel, what you're describing can all be done with mailmerge.

[jish]

Thank you! Your tutorial looks very helpful. I'm not sure that it will help with this particular issue but it will definitely help with other ones---so, great! I was not able to access the gmayor.com link. I will check into the mailmerge option. Thanks, again!