Find closest value with a condition

[kvazar]

Hi, so say I have this table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Number[/TD]
[TD]Closest difference (in %)[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]42[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need to fill the third column, so for example in first row it should give me % difference between 12 and 16, in second - same, in third between 22 and 16 etc. I have tough time finding a formula that can find the closest value using the condition type.

 

[Marcelo Branco]

To get the closest difference try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Type​

[/TD]
[TD]

Number​

[/TD]
[TD]

Closest Difference​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Apple​

[/TD]
[TD]

12​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Apple​

[/TD]
[TD]

16​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Apple​

[/TD]
[TD]

22​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Banana​

[/TD]
[TD]

24​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Banana​

[/TD]
[TD]

26​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Banana​

[/TD]
[TD]

42​

[/TD]
[TD]

16​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in C2 copied down
=MIN(IF(A$2:A$7=A2,IF(ROW(B$2:B$7)<>ROW(B2),ABS(B$2:B$7-B2))))
Ctrl+Shift+Enter

It is not clear how to calculate the percentages. These values should be divided by what value?
Could you explain?

M.

 

[kvazar]

Thank you, Marcelo!

Works great. As of the percentages - good point, basically need to understand if items are interchangeable, but there are no definition except if the 'number' are close.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.