Formula Help - MIN/MAX and IF

[jaybee3]

Hi all, this one is starting to do my head in. it was working fine until they decided that there would be a one-to-many relationship in my mapping table.

New table:
Linked_ID is fed via vlookup, and looks up the ID of the parent ID. It used to lookup the child of the ID (since it was a one to one relationship).

My minimum Value formula is as follows (based on the old relationship where the SUB ID used to be the child rather than the parent.

{=IF(C2="",MIN(IF($A:$A=A2,$D:$D)),MIN(IF(($A:$A=A2)*($A:$A=C2),$D:$D)))}

Basically, I need to find the minimum value across the data for the following criteria:

If ID is a parent (i.e. has children, like 1 below) then it takes the minimum value from not just it's own dataset, but also that of it's children too. for 1, it takes the minimum of: 10, 5, 3, 15, 18, 19, 16, 1, 5, 8. Rather than just the minimum of 10, 5, 3, 15

However, if it's a child or is not a parent, then it just needs to take the minimum for it's own ID.

ID    SUB ID     Linked_ID    Value   Minimum Value
1        1                    10         1
1        2                     5         1
1        3                     3         1
1        4                    15         1
2        1          1         18        16
2        2          1         19        16
2        3          1         16        16
3        1                     6         6 
4        1          1          1         1
4        2          1          5         1
4        3          1          8         1
5        1                     8         8

If you need anymore info please please just fire away

 

[Asala42]

It looks like you need to apply an Or statement, so you'll want to add criteria rather than multiply.

Try this:
{=MIN(IF((($A:$A=A2)+($C:$C=A2))>0,$D:$D))}

(Ctrl+Shift+Enter)

 

[jaybee3]

You're a star! Cheers matey.

Just for future reference:
* - AND
+ - OR

Are there any other useful ways to use the operators?