Calculate Average from Multiple Data Sets

[Jeffrey Lebowski]

Hi,

I am having trouble conceptualizing which formula or combination of formulas to use in this scenario. . .

Imagine 2 data sets that come from 2 different database tables:

• Table 1 contains a list of Car Parts and their respective cost.
• Table 2 contains a list of Cars, and the Car Parts that belong to them. In the sample file, each Car has 2 Car Parts.

What is the best formula to calculate the average $ spent on car parts, per car? I have attached a sample file that illustrates this problem. The highlighted column contains the manual average calculations.

Thank you for the help!

 

[tomas]

Hi this formula

=SUMPRODUCT((G4=$D$4:$D$33)*($B$4:$B$33))/COUNTIF($D$4:$D$33,G4)

will work

 

[Jeffrey Lebowski]

Awesome, thank you so much!!!

 

[Jeffrey Lebowski]

Do you mind helping me understand the logic of the formula?

 

[tomas]

No problem

so from point of view cell J4:

1. This asks if name of car in G4 is equal to the names of cars in D column and returns True and False accordingly.

(G4=$D$4:$D$33)

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}


2. This simply returns the numbers from B column.
($B$4:$B$33)

{68;88;79;51;284;301;268;176;109;51;334;73;202;197;133;180;298;299;164;204;179;249;141;153;64;168;236;55;204;305}

3. When you perform mathematical operations on logical values ( true and false ) True converts to one and False to 0

therefore when I multiplay this arrays

(G4=$D$4:$D$33)*($B$4:$B$33)

it's going like true*68=68, true*88 = 88 , and False * whatever = 0

therefore you get this as result of multiplication

{68;88;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

4. function sumproducuct is versatile and in this casu just sum this numbers

5. Countif you probably can figure out but basically COUNTIF($D$4:$D$33,G4) it looks in column D and count how many times there is care name from cell G4.

6. You could use 2 as denominator but maybe you might have more then two parts therefore countif is better.

If something not clear just ask

 

[Jeffrey Lebowski]

great explanation!

 

[tomas]

great explanation!

I know

thanks

 

[Jeffrey Lebowski]

Would SUMPRODUCT still work if we added another level? So imagine we add another table:

• Table 1 contains a list of Car Parts and their respective cost.
• Table 2 contains a list of Cars, and the Car Parts that belong to them. In the sample file, each Car has 2 Car Parts.
• Table 3 contains a list of Drivers and the Cars that they own.

In the same fashion, can we calculate the $ spent on Car Parts per Driver??

New sample file attached. I tried to apply the same logic of the sumproduct solution but this problem is more complex. Would an array formula be appropriate?

 

[tomas]

With helper column which I did in G column it is possible to use the same formula in column N.

Without helper column it would be sure a nastier formula and sure required some serious thinking on my part