weighted average getting messed with additional rows.

**StuartDrury** · 07-18-2011, 08:58 AM

I am having trouble keeping the weighted average correct when I have day totals. For example in the attached spreadsheet on the page named exposures if I have a line under 17 august 2011 for a day total of 63 102 then my weighted average rates for August as a whole get messed up. It basically now includes the 63 102 in the calculation because the weighted ave includes the sum of rows 8 to 19. I basically want to have a weighted average for each day and then a weighted average for the month as a whole. In the monthly total I want to exclude the rows in which the day totals would be shown. This sheet will be continuously changing with new exposures and will also link to other pages so I am looking for a way to get around this without manually changing the formulas each time the page is altered.

The weighted average is the sum of the rate (either spot, budget or costing) times the amount and dividing that amount by the sum of the amounts.

Quite complex I know but any help would be appreciated.

**Ron Coderre** · 07-18-2011, 09:45 AM

I believe most of your calculation issues can be solved by retooling your formulas.

You can replace SUM functions with SUBTOTAL functions, which ignore other SUBTOTAL functions within their referenced ranges.

Also, the SUMPRODUCT function can replace certain of your SUM array formulas.

Examples using your "Exposures" sheet (see attachement)
F16: =SUBTOTAL(9,F14:F15) .....sums 17-Aug-2011
F20: =SUBTOTAL(9,F8:F19) .......sums the column, ignoring the F16 cell

C20: =SUMPRODUCT(C8:C19,$F$8:$F$19)/SUBTOTAL(9,$F$8:$F$19)
Calculates the weighted average properly. Copy that formula across through E20.

Note: those are all regular formulas, not array-formulas.

Is that something you can work with?

**StuartDrury** · 07-18-2011, 10:56 AM

Yes thanks that does help, however I also need subtotals for the rates on each date and for these average rates on each date not to affect the month total. Say for example on the exposure page for 17 Aug I need the day weighted average for the rates. I know in this example they it will be the same as the rate itself. You see I need that weighted average rates (especially the costing rate) so I can pull it through to page 'exposure/positions' in the column costing rate. This is so that one can see at a glance (on the exposure/position page what the rates and amounts are for each date.

**Ron Coderre** · 07-18-2011, 11:17 AM

I recommend moving the daily_wtd_avg formulas out of the body of the data and putting them off to the side (see attached file). In the attached file, those formulas automatically determine which rows to calculate the daily wtd avg on.

Is that something you'd consider?

**StuartDrury** · 07-19-2011, 09:52 AM

Yes thank you so much. I can work with that. Hopefully I can figure the rest out myself now. Those formulas you used where pretty hardcore. Still trying to work out what exactly they are doing.