Active Member
June 27, 2016
I need to create a summary of a Database and have it provide subtotals at specified changes.
Inserting subtotals is easy of course, but what I need is a weighted average so I need the SumProduct formula instead of the subtotal formula.
I would like to have it automated using an auto-open macro, that will update the information from the DB, then sort and insert the subtotals & totals
using the sumproduct formula instead of the subtotal.
I know how to do all of this except the substitution of the formula.
Hoping someone has a solution for this.
thank you
Julie
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Active Member
June 27, 2016
Hi Catalin,
I have uploaded a sample file of the summary data.
I inserted subtotals which of course is very easy - but then I had to manually add the weighted average lines below each subtotal line.
I don't need the subtotal lines- just the weighted average lines.
I am hoping for some VBA code that will insert these weighted average lines, but I will appreciate any method that will automate this.
Thank you
Julie
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi Julie,
You have to start learning pivot tables, it will save you a lot of time.
See the attached file for a sample pivot table report. I added the calculated column only for Area 1, you have to do the same for your other columns. For the weighted average, I added a calculated field (you will find the field in Pivot Table tools Tab in ribbon, Analyze-Field,Items and Sets). You have to do the same to add this calculated field for the other columns you want to show in report.
Hope this is what you wanted.
Cheers,
Catalin
1 Guest(s)