Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-29-2016, 05:11 AM
alexsimpson alexsimpson is offline How to calculate weighted average in table? Windows 10 How to calculate weighted average in table? Office 2010 32bit
Novice
How to calculate weighted average in table?
 
Join Date: Nov 2016
Posts: 5
alexsimpson is on a distinguished road
Red face How to calculate weighted average in table?


Hi, I have a list of funds in a table in word which is populated from a back office system which means it can be 2 rows or 10 rows.

One column has the Fund Name, the second has the Value and the third has the Charge.

I have a total of the fund values purely being =SUM(ABOVE) but then the Charge needs to be a total weighted charge i.e. it needs to multiply each holding by the charge then add them all up and divide that charge by the overall value.

We are currently having to do this by putting it all into excel, creating another column that multiplies each fund value with the corresponding charge and then totaling this.

Is there a way in which this can be automated in the table? I have uploaded a trial document
Attached Files
File Type: docx TRIAL.docx (16.3 KB, 15 views)
Reply With Quote
  #2  
Old 11-29-2016, 09:00 AM
macropod's Avatar
macropod macropod is offline How to calculate weighted average in table? Windows 7 64bit How to calculate weighted average in table? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Your weighted average can be calculated quite easily, using a formula field coded as:
{=(B2*C2+ B3*C3+ B4*C4+ B5*C5+ B6*C6+ B7*C7+ B8*C8+ B9*C9+ B10*C10+ B11*C11+ B12*C12+ B13*C13)*100/B14 \# 0.00%}

To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial: https://www.msofficeforums.com/word/...-tutorial.html

Note: The field brace pairs (i.e. '{ }') for the above example are created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac) or via Insert|Quick Parts>Field>=(Formula); you can't simply type them or copy & paste them from this message.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-01-2016, 05:41 AM
alexsimpson alexsimpson is offline How to calculate weighted average in table? Windows 10 How to calculate weighted average in table? Office 2010 32bit
Novice
How to calculate weighted average in table?
 
Join Date: Nov 2016
Posts: 5
alexsimpson is on a distinguished road
Default

Thanks for that. That is basically the formula I need but the problem with it is the number of rows alters all the time based on the amount of data that pulls through from an XML feed. Therefore the formula needs to vary depending on how many rows are in the table. i.e. like SUM(ABOVE) would do. Is that possible?

Thanks!
Reply With Quote
  #4  
Old 12-01-2016, 03:19 PM
macropod's Avatar
macropod macropod is offline How to calculate weighted average in table? Windows 7 64bit How to calculate weighted average in table? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

No, that isn't possible, since the amount on each row needs to be multiplied by the percentage for that row to get the value for that row, which then has to be aggregated with the values for the other rows calculated in like fashion, then divided by the grand total to get the weighting. Word tables & formulae don't have the functionality of Excel, which is what you really should be using if the row count varies. If you were to use an Excel workbook, you could either embed it or an OLE link to it in the document. In the latter case, if you use a named range that varies according to the amount of data, the embedded object in Word will resize to accommodate the changes.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
formula, tables

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
dynamic ranges to calculate average teatree Excel 1 11-11-2016 10:11 AM
dynamic ranges to calculate average teatree Excel 1 11-11-2016 10:03 AM
How to calculate weighted average in table? Calculate average age FTL Excel 1 08-09-2016 06:56 AM
How to calculate weighted average in table? Calculate Average Time Elapsed in Days, hours and minutes Nina46 Excel 9 09-11-2015 02:09 AM
calculate average depending on drop-down values virencm Word 0 08-06-2010 06:10 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft