Find Out Nearest rate more or less from data for particular product

[mangesh.mehendale]

Hello,
I want to find nearest rate for say X product, Y Product in Data it may be more or less.

Exp1. Rate of Product X is 3... Vendor1- Rate 10, Vendor2 - Rate 9 Vendor3 - Rate 2 I have to choose "Vendor 3" as his rate is near
Exp2. Rate of Product Y is 10... Vendor1- Rate 11, Vendor2 - Rate 20 Vendor3 - Rate 30 I have to choose "Vendor 1" as his rate is near
Exp2. Rate of Product Z is 10... Vendor1- Rate 11, Vendor2 - Rate 9 Vendor3 - Rate 30 I have to choose "Vendor 2" as his rate is near and less


Lesser Rate is preferable... Excel file attached...

[XLent]

Hi, per sample & expected results you could use below:

Please Login or Register  to view this content.

[mangesh.mehendale]

Thank You..

[XLent]

Hi, in actual fact whilst that works for your samples it won't work in all instances... so revised version below with ABS inclusion.

Please Login or Register  to view this content.

Note: I've assumed your Rate values would always be >=0

[mangesh.mehendale]

Okay.. Thanks again... Will take care for 0