Calculate the common area between 2 products

Med4040

Board Regular
Joined
Jan 9, 2018
Messages
55

Hi guys,



I need to calculate the common area between 2 products

I have multiple of product that are made on series ofequipment. Each equipment has a surface area.

I need to calculate the shared area between 2 selected products. Some products may have more than more than one surface area (column12). So more specifically, the formula should calculate shared area between thelargest surface area of one product and largest surface area of another product
In this example, I am calculating the shared area between product A and D. the largest surface area of product A is 5 and the largest surface area of product D is 6. Two equipment (A and C) are common product A and D. Therefore, the common area between product A and product D is 2.

This is the formula I am using, but it the first product in the table
<style type="text/css">



table.tableizer-table{
font-size:12px;
border:1px solid #CCC ;
font-family:Arial, Helvetica, sans-serif;
}
.tableizer-tabletd {
padding:4px;
margin:3px;
border:1px solid #CCC ;
}
.tableizer-tableth {
background-color:#104E8B;
color:#FFF;
font-weight:bold;
}
</style>


EquipmentName
Equipment Surface Area(m2)
Product A
Product A

Product B
Product C
Product C

Product D
Product D

Size
S
L
S&L
S
L
S
L
Equipment A

1
Y

Y

Y

Equipment B

1
Y
Equipment C

1
Y
Y

Y
Y
Y

Equipment D

2
Y
Y
Equipment E

2
Y
Y
Y
Equipment F

3
Y
Y
Equipment G

4
Y
Y
Product Surface Area (m2)

4
5
6
3
4
5
6

<tbody>
</tbody>




 
Last edited:

Med4040

Board Regular
Joined
Jan 9, 2018
Messages
55
My bad, I forgot to add the formula I have

Code:
SUMPRODUCT((INDEX(Trains!C5:I12,,MATCH(E3,Trains!$C$1:$I$1,0))=INDEX(Trains!$C$5:$I$12,,MATCH(N3,Trains!$C$1:$I$1,0)))*(INDEX(Trains!$C$5:$I$12,,MATCH(N3,Trains!$C$1:$I$1,0))="Y")*Trains!$B$5:$B$12)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,971
I found it easiest to create one formula to find the right column for each product (M4 and N4), then the formula to find the common area is straightforward:

ABCDEFGHIJKLMNO
1EquipmentNameEquipment Surface Area(m2)Product AProduct AProduct BProduct CProduct CProduct DProduct D
2SizeSLS&LSLSL
3Product AProduct D
4Equipment A1YYY27
5Equipment B1Y
6Equipment C1YYYYYCommon Area
7Equipment D2YY2
8Equipment E2YYY
9Equipment F3YY
10Equipment G4YY
11Product Surface Area (m2)4563456

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Trains

Worksheet Formulas
CellFormula
M7=SUMPRODUCT($B$4:$B$10,--(INDEX($C$4:$I$10,0,M4)="Y"),--(INDEX($C$4:$I$10,0,N4)="Y"))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
M4{=MATCH(M3&"|"&MAX(IF($C$1:$I$1=M3,$C$11:$I$11)),$C$1:$I$1&"|"&$C$11:$I$11,0)}
N4{=MATCH(N3&"|"&MAX(IF($C$1:$I$1=N3,$C$11:$I$11)),$C$1:$I$1&"|"&$C$11:$I$11,0)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Med4040

Board Regular
Joined
Jan 9, 2018
Messages
55
The calculation is on another sheet called "Calculations". The data is on a sheet called "Trains"

On this sheet, I need to be able to select any 2 products.




This is my data table
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,971
Sorry, I thought you'd be able to change the ranges. Try these formulas on your Calculations sheet:

EFGHIJKLMN
3Product AProduct D
427
52

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Calculations

Worksheet Formulas
CellFormula
E5=SUMPRODUCT(Trains!$B$5:$B$11,--(INDEX(Trains!$C$5:$I$11,0,E4)="Y"),--(INDEX(Trains!$C$5:$I$11,0,N4)="Y"))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
N4{=MATCH(N3&"|"&MAX(IF(Trains!$C$1:$I$1=N3,Trains!$C$12:$I$12)),Trains!$C$1:$I$1&"|"&Trains!$C$12:$I$12,0)}
E4{=MATCH(E3&"|"&MAX(IF(Trains!$C$1:$I$1=E3,Trains!$C$12:$I$12)),Trains!$C$1:$I$1&"|"&Trains!$C$12:$I$12,0)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



The E4 and N4 formulas can be put anywhere. If you want to hide them, just do a custom format of ;;; and they will not show on the sheet. Then put in the E5 formula, again adjusting the ranges to your sheet.
 

Med4040

Board Regular
Joined
Jan 9, 2018
Messages
55
Thank you so much Eric. I really appreciate it. It is working great.

I am just trying to understand what do the formulas in E4 and N4 calculate? Also, should these cells be hidden? Because if I am going show these numbers, I need to explain what do they mean

Thanks again
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,971
The E4 formula figures out what column in your Trains table has the maximum area for "Product A" (or whatever is in E3). So in this example, it says column 2, which starting at column C, gives us column D. Same idea for N4. In theory, both of these formulas could be incorporated in the E5 formula, but it would be one long, awkward formula.

Glad I could help.
 

Forum statistics

Threads
1,081,845
Messages
5,361,663
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top