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:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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)
 
Upvote 0
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>
 
Upvote 0
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.

mh6kuu.png



This is my data table
2eulm6e.jpg
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top