#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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! |
#4
|
||||
|
||||
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] |
Tags |
formula, tables |
|
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 |
Calculate average age | FTL | Excel | 1 | 08-09-2016 06:56 AM |
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 |