Min/Max related field

[icduvall82]

Hi,

If i have 2 related tables that look something like this:

Table 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Ref#[/TD]
[/TR]
[TR]
[TD]a[/TD]
[/TR]
[TR]
[TD]b[/TD]
[/TR]
[TR]
[TD]c[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]Ref#[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

Can I add the Min / Max values from table 2 to table 1, so results would be:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Ref#[/TD]
[TD]MinValue[/TD]
[TD]MaxValue[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be much appreciated.

Ian

 

[MD610]

Import both tables to PowerPivot. Create a relationship between the 2 tables on the Ref# column.

In Table 2, create 2 measures (not Calculated Columns) to find the min and max of the Values column:

MinValue: = MIN(Table2[Values])
MaxValue:= MAX(Table2[Values])

If you actually want to see the results in Table 1, go to table 1 and create 2 Calculated Columns. In one you put =[MinValue], in the other you put =[MaxValue].

If you just want to see the results in your final Pivot Table, you can forget about the Calculated Columns in Table 1 and just make a new Pivot Table. Drop the Ref# column from Table 1 in your Rows and then drop the MinValue and MaxValue measures into your Values.

 

[jai9]

Hi,

Try,

With first table in C1:D10 and second in F1:H4.

=MIN(IF($C$2:$C$10=F2,$D$2:$D$10))
=MAX(IF(C2:C10=F2,D2:D10))

Array formula. To be confirmed with CTRL+SHIFT+ENTER.

Jai

 

[icduvall82]

Many thanks, I knew there was going to be a simple answer to this!