# weighted average

Status
Not open for further replies.

#### merlin_the_magician

##### Active Member

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....?

Last edited:

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Fluff

##### MrExcel MVP, Moderator
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).

Status
Not open for further replies.

Replies
2
Views
251
Replies
3
Views
4K
Replies
3
Views
434
Replies
0
Views
927
Replies
5
Views
599