conditional Weightened average

Hello,

I work with a huge excel file (40 colons, 55000 lines). I give you a little example of it here, 150 lines.

The file is about: who financed politicians.

What i need: a measure of "how much is politician dependent from big corporations).

Each line of the file is a funding, a bank transfer, from a company. Each of them is coded according to the size of corp:
3: top 100
2: top 101-500
1: 501-1500

As you can see in the file, i have a column where i compute the % that each transfer represent wrt to the total received by each candidate.

I need now to measure the dependence of these men to big corporations, makin, i think, a weightened average of the "% of total received" and of the "size of corporation" (biggesemp, in my file).

I made up a formula, but it's to heavy my computer cannot make it. It was with 3 SUM.IF and some NB.iF in the middle... doesnt work because of my hardware. Excel had to make millions of computations to do it, mainly because of the "Name" condition: we want averages, by name, of course.

In the file, you can see an other solution i got, but it does not seem optimal, plus it gives different results than when i compute by hand (i did it to try for one or two of these guys).



Do you understand what i need and do you have an idea ?
it's a bit messy to explain i guess,


thanks a lot, and have a nice day !


Aya

Hi -

One issue with your formula is it uses the OFFSET function, which is a volatile function. That means, everytime you do anything in the spreadsheet, Excel has to recalculate every one of your formulas. For a large spreadsheet, it becomes unwieldy. Here's a simpler approach:

In cell AI2, replace your formula with the following formula:

=SUMIFS(AF:AF,A:A,A2,AN:AN,1)/SUMIF(A:A,A2,AF:AF) and copy it down to all of your cells in that column. This formula reproduces the results you had without using the OFFSET function, and without checking to see if the name changes.

Hope this helps.

Hi ! thank you for your answer. It works well on the little file, but i only get 0s when it comes to the big file.
What can I do ?
Also, if we look at the results for Adal, we have 0,56624405 with your formula.
If i use the manual formula, = SUMPRODUCT(AH12:AH20;AL12:AL20), i have a different result: 1,181095295. why is this ?

Well actually i understand the maths behind SUMPRODUCT but not behind your formula. And i think there is a computation issue for my excel file. Maybe i can do in a lighter file and after reintroduce the results with searchv ?

I put you a file here with the different formulas i used: yours, sumproduct, the offset one also, so you can see the differences.

Here weights are 0 or 1, but usually i also have 2 and 3.

thank you!

aya

Hi -

OK - I see the problem. My original post was just taking the average if there was a 1 in column AN, not a weighted average using the value in column AN.

I think there is a way to greatly simplify this. Since you already have a helper column in AH that divides the Value by the Total, let's just modify that to also multiply by the weights. So the formula in Column AH will look like this:

=AF2/AG2*AN2

Put this in cell AH2 and copy down.

Then, in cell AI2, input this formula:

=SUMIF(A:A,A2,AH:AH)

This is much simpler, and should help with speed on your larger file. This formula compares Column A to cell A2, and sums up the weighted percentages in column AH. So, for example, cell A2 contains Abel. Every row in Column AH that has a match in Column A for the name Abel will be added together. The result in Cells AI2 through AI11 (Abel) is 0.028. Likewise, every cell from AI12 through AI20 (Jose) is 1.181 (which is what your old SUMPRODUCT formula calculated). Adal is 0.566 and so on.

I have attached a revised spreadsheet. Please note your SUMPRODUCT formula will not necessarily be correct since it is multiplying by the weights, and the first formula above already does that. So the current SUMPRODUCT is double counting the weights. This is only an issue if the weights are not 0 or 1.

Hope this helps!