weighted average

[merlin_the_magician]

I posted a question yesterday, but reconsidering, it was quite unclear indeed. New try, including the actual workbook....

Some background info: the workbook holds a table filled with droplist cells, It is used for scoring construction machinery on emissions. Per vehicle/machine, an engine and drive can be selected.
I did not bother to translate from Dutch, but that is not important, really. You'll probably get the point.

Materieelstuk = equipment item (vehicle/machine)
Motorcategorie = type of engine, by relevant classification
Aandrijving = drive (different types of fuel/electric/hydrogen)
Punten = Points scored

Values for the droplists and points are extracted from a few tables below in the worksheet.

Equipment items are individually scored. Corresponding points scored are extracted using VLOOKUP(). Probably not the prettiest, but the easiest way to do so.
However, whenever an enginetype and/or drive occurs more than once in the same line, I need a weighted average. In this example engine “level 3a” occurs twice.

Considering C6:

I figured a simple solution by looking at the value in B6, and search just search range B6, E6, H6,K6 and N6 for a matching values. When found, count their number (in this case: 2) and use that number divide the corresponding score (30), resulting in a weighted value of (30/2=15)
Of course, this does not go for C6 only. Same thing shoud be in F6, I6, L6 and O6)

That actually works using this formula.
=IF(B6="","",VLOOKUP(B6,INDIRECT(B5),2,0)/COUNTIF(A6:O6,B6))

Problem 1: this only seems to work using a range of conjoined cells. I tried a named range for the selection of cells, but that failed.
Problem 2: I cannot place this formula in C6 for it creates a circular reference (is that the correct English Excel term? Anyway… it creates a loop, C6 referencing itself).

I’m puzzled on how to solve this. Of course I could make this work with some mirror worksheet copying values from this one, but I’d rather have an actual solution. Anybody....?

Full workbook here: http://s000.tinyupload.com/?file_id=72036996429948868719

 

[jasonb75]

As your original already has replies, I'm flagging this to the moderators as a duplicate thread.

COUNTIF() and weighted average

A B C D E F G H I J 1 color points color points color points color points color points 2 blue 10 green 20 blue 10 red 30 blue 10 Points blue: in the simpe example sheet above, I have a table with values. I need to determine the count of each...

www.mrexcel.com

 

[Fluff]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).