Really annoying multi-column averaging problem

[mrdrumhead]

Hi folks, so this problem is probably pretty elementary to some, but...
Column A has numbers to average. Column B has frequency of occurrence of each number in column A. The brute force option would be to manually enter in a new column each value in A however many times as defined in B, that way you would have the correct average, but this would take far too long. Anyone have an idea of how to do this?
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Numbers to Average[/TD]
[TD]Frequency of Each Number to Average[/TD]
[/TR]
[TR]
[TD]3203[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]3170[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3122[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]







etc. etc. for hundreds and hundreds of rows

 

[JoeMo]

Hi folks, so this problem is probably pretty elementary to some, but...
Column A has numbers to average. Column B has frequency of occurrence of each number in column A. The brute force option would be to manually enter in a new column each value in A however many times as defined in B, that way you would have the correct average, but this would take far too long. Anyone have an idea of how to do this?
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Numbers to Average[/TD]
[TD]Frequency of Each Number to Average[/TD]
[/TR]
[TR]
[TD]3203[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]3170[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3122[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]







etc. etc. for hundreds and hundreds of rows

If column A contains the individual numbers you want to average and B the number of times that number appears, then just use sumproduct(A:A,B:B)/sum(B:B) to get the average.

 

[mrdrumhead]

If column A contains the individual numbers you want to average and B the number of times that number appears, then just use sumproduct(A:A,B:B)/sum(B:B) to get the average.

Thanks for your answer! I didn't quite understand what's actually happening in the equation though, so I did a manual average test case and it actually came out a little different...
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]173[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]2405[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]3066[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
What happens is the formula you listed gives me an output of 1329.509, but when I manually list out each occurence and just AVERAGE for that column, it gives me 1340.981. Any idea why this might be happening?

 

[Aladin Akyurek]

Thanks for your answer! I didn't quite understand what's actually happening in the equation though, so I did a manual average test case and it actually came out a little different...
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]173
[/TD]
[TD]29
[/TD]
[/TR]
[TR]
[TD]2405
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]3066
[/TD]
[TD]11
[/TD]
[/TR]
</tbody>[/TABLE]
What happens is the formula you listed gives me an output of 1329.509, but when I manually list out each occurence and just AVERAGE for that column, it gives me 1340.981. Any idea why this might be happening?

Both formulas

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

=SUMPRODUCT(A:A,B:B)/SUM(B:B)

yield 1340.981 for the last sample, as they should.