The Variable WEIGHTED AVERAGE and SUMPRODUCT PUZZLE

If you put this formula in cell D3, would that do the trick?

Formula:

Please Login or Register  to view this content.

The Address-function uses the values in D2 and E2, but need to add 4 to it to get the right row. You could make this more flexible by naming the ranges used in the sumproduct-function and referring to its top row with ROW(myRange). Same can be done with the column number.

The ADDRESS-function returns a cell address as a string.
Syntax is ADDRESS(RowNumber, ColumnNumber)
The RowNumber is coming from cell D2, the ColumnNumber is the number of the column you want (A=1, B=2, C-3, etc).
So in this case - when the value of D2 = 2 - you get ADDRESS(2,1). That would return the string "$A$2", but that's not correct yet as the value in D2 is the first cell in the range with serial numbers. For this 4 is added to the value of D2 and so you get ADDRESS(6,1) giving the string "$A$6". That's the second cell in the serial number range.
Same goes for the last cell in the range which is in E2. Then you can concatenate the strings returned into a range address.
In order to use that string as a range you will need to encapsulate the address-functions in the INDIRECT-function).

To see what happens, put the formula in some cell and run "Evaluate formula" in the Formulas menu. This allows you to step thru the formula.

ADDRESS($D$2+4,1) means to take the value in D2 and add 4 to it and apply to column 1 which is column A which would make the value A6

ADDRESS($E$2+4,2) means to take the value in E2 and add 4 to it and apply to column 2 which is column B making the value B11.

The 4 represents the row difference between D2 and the first row of data in column A. Row E2 counts as the first row and the first row of data is 5 so the difference is 2,3,4,5 (4 rows).

The 1 and 2 after the commas are the columns. Column A is 1 Column B is 2.

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Glad I could help and thx for the rep.
The formula however has the disadvantage that the ranges are expected at a fixed location of the worksheet. That is because the top row is assumed to be row 5 and the columns are assumed to be A (1) and B (2).
You can solve this be doing the following:

1. Create a name - "SerialNumber" - for the range with serial numbers (A$5:$A$14).
2. Create a name - "Price" - for the range with prices ($B$5:$B$14).
3. Create a name - "SelectedSerialNumbers" - containing the formula
   Formula:

   Please Login or Register  to view this content.

4. Create a name - "SelectedPrices" - containing the formula
   Formula:

   Please Login or Register  to view this content.

You can create names in the Formula menu with the option Manage Names.
With this the formula in D3 can be

Formula:

Please Login or Register  to view this content.

and you will be able the serialnumbers and prices anywhere on your worksheet.

Posted your workbook as an example.

Dear Tsjallie,
Thanks a lot again and again for taking lots of pain and time to look into this matter. Actually I was going to write to you about it , that once the starting point changes the results obtained are wrong. Its great to know that you came to know about it ( after all I believe that you are an adept !).
So without actually mentioning these things I tried out the details you have given above. Actually the formula does not work ( I sincerely tried three times .....each time erasing all names and rewriting them by copy n paste from your writings BUT still the formula in D3 says #NAME?.
Deeply intrigued by the whole setup ( which is totally alien to me), I tried to check each formula. None of the work if put any where out of the ROW range 5:14.
Since I don't have any understanding of these things please upload your version of the sheet so that I can actually see it happening ( lest I might be making some error or the other !), Thanks again

If a formula returns #NAME then that means that a function or name is misspelled.
Actually here's the culprit

Formula:

Please Login or Register  to view this content.

Sorry about that. I'll get my coat

To make up I'm uploading the workbook from my previous posting again. Have added some illustration to show how the construction works.

Here is your workbook with a couple of alternate formulae that you may find useful.

I believe that the formula in B2 is incorrect. It is the same formula given at http://support.microsoft.com/kb/214049 for weighted average. This would be correct IF the values from A6:A14 are the number of event that each of the corresponding values in B6:B14 are calculated from. A6:A14 was stated as being serial numbers and not the number of events determining the values in B6:B14.

The sum of B5:B14 is 106.8 That makes the average 10.68 and NOT 10.74

The formula that you have in B2 is actually (1*10.0 + 2*11.0 +3*10.5 + 4*11.0 +5*10.0 +6*11.0 +7*11.1 +8*11.2 + 9*10.0 +10*11.0)/sum(A5:A14)

All this equals: 590.8/55 = 10.74 This isn't the average which is 10.68, and it isn't the median which is 11 so, I don't know what the calculation is actually calculating as there doesn't appear to be correct data for calculating a weighted average.

You're welcome, but fact is that I believe I've not really been helping you.
Something about your case has been bothering me from the start and this morning the coin finally dropped.

If I understand it correctly what you're to achieve is calculating the weighted average of a subset of your data.
Then the problem lies in the solution you've chosen. In the first place this needs some complex (though heroic) formulas. In the second place it limits you the create only subsets of consecutive rows.

I'm posting your workbook again with an alternative solution on sheet2.
It uses autofilter with and subtotal which only sums visible rows. For the multiplying part of sumproduct I added a column in wich the values are multiplied by the serial numbers.

It also shows two methods of calculating the weighted avarage: one with the sum of the selected weights (which one you're using), and one with the number of selected values.
Just struc my eye and not you're using your method on purpose.

@Tsjallie

The question comes down to: Are the serial numbers in column A "weights" for the values in column B or are they just consecutive serial numbers? If they are "weights" then =SUMPRODUCT(B5:B14,A5:A14)/SUM(A5:A14) is a correct formula (according to Microsoft Kb214049).

If the values in column B are all of equal weights then the weighted average will be equal to the average (sum/count). In most other circumstances, the weighted average will not be equal to the average (sum/count).

If the values in column A are weights, then I think that you are on the right track.

@newdoverman
The formula used suggests the values in column A are weights. I don't see the meaning of the values in column A, but assume they're just dummy data.

Could this possibly be the solution sought which is my interpretation of the solution by Tsjallie?

Aren't we driving someone crazy here? Poor Omega0010!

@ Tsjallie

I think that there may be a misunderstanding possibly due to language. (a common enough occurrence) The formula used indicated a weighted average but the language used referred to the range in column A as serial numbers. This, I think, is the confusion. My last upload takes the weights along with the values according to the values entered and applies the weighted average formula.

My figures agree completely with your method #1.