weighted average

Status
Not open for further replies.

merlin_the_magician

Active Member
Joined
Jul 31, 2002
Messages
479
Knipsel.JPG


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.:p

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. :rolleyes: 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
 
Last edited:

Some videos you may like

Excel Facts

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,601
Office Version
  1. 365
Platform
  1. Windows
As your original already has replies, I'm flagging this to the moderators as a duplicate thread.

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
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.

Watch MrExcel Video

Forum statistics

Threads
1,118,808
Messages
5,574,434
Members
412,592
Latest member
moonsugar
Top