subtotal using Weighted Averages | General Excel Questions & Answers

subtotal using Weighted Averages

Julie_W

Member

Members

Forum Posts: 3

Member Since:
June 27, 2016

Offline

June 29, 2016 - 1:29 am

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

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

June 29, 2016 - 2:31 pm

Hi Julie,

Please upload a sample file, we cannot help you without seeing and testing the formulas.

Thanks for understanding

Catalin

Julie_W

Member

Members

Forum Posts: 3

Member Since:
June 27, 2016

Offline

July 5, 2016 - 11:13 pm

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

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 6, 2016 - 1:37 pm

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

Julie_W

Member

Members

Forum Posts: 3

Member Since:
June 27, 2016

Offline

July 7, 2016 - 1:10 am

Hi Catalin,

I do use pivot tables and slicers all the time, I did not however know how to add the calculated field so I didn't think it would work for me in this instance.

Thank you so much for your help!

Julie.

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

July 7, 2016 - 3:09 am

You're welcome 🙂