Find and highlight the two closest values within a row

[LUNAglay]

I've been looking for a formula or conditional formatting-function to solve my little case, but couldn't find it yet...

Please find the attached excel for my data. I want the Excel to identify, the smallest value among the closest 3 values who are the closest to each other. This must be within the same list & there's no reference-value.

Thanks a lot in advance for any help.

LUNAGLAY

 

[Deepak]

I didn't understand the query but this might help.
=MIN(C13,E13,G13,I13,K13)

 

[Eloise T]

I've been looking for a formula or conditional formatting-function to solve my little case, but couldn't find it yet...

Please find the attached excel for my data. I want the Excel to identify, the smallest value among the closest 3 values who are the closest to each other. This must be within the same list & there's no reference-value.

Thanks a lot in advance for any help.

LUNAGLAY

Using Deepak's formula, is this what you were looking for?
(See attachment)

 

[NARAYANK991]

Hi ,

The sample data you have given in your uploaded file is such that the least of the values in red is what you have shown in column M.

Is this always going to be true ?

If not , upload data which shows it to be otherwise.

Narayan

 

[LUNAglay]

Thank you for the quick response. However, the proposed solution didn't resolve my problem.

Please see attached tabulation.

Can the Excel indicate the smallest value among the 3 closest values to each other (in a row) at a new cell?

For example:

Row 1: the 3 closet values to each other would be 64,706, 47,300 and 48,000. the smallest value among this 3 closet values is 47,300

Row 2: the 3 closet values to each other is 49,500, 76,471 and 50,000. the smallest value among this 3 closet values is 49,500

and so on, with the following conditions:-

1) there is no reference-value

2) within the same row

3) exclude column C and F

Your help would greatly appreciated.


My finding so far is as follow:-

to use the following formula:-

e.g.

=REPLACE(SUMPRODUCT(N(OFFSET(INDEX(nlist,1), 0, ROUND(MOD(SMALL(ABS(nlist-TRANSPOSE(nlist)) + COLUMN(nlist)%, {1,2}+COLUMNS(nlist)), 1)*100, 0)-1)) * {1000,1}), 3,1," and ")

however, the above formula only give 2 closest values in a 'nlist' (range in row) instead of 3 closest value in a row and cannot exclude/ignore column C and F. i also need the excel to identify the smallest value of the 3 closest value in a row . I would appreciate it if anyone could help me. thank you..

 

[bosco_yip]

Perhaps....

1] In I1, copied down :

=SMALL(N(OFFSET(A1,,{0,1,3,4,6})),1)&" , "&SMALL(N(OFFSET(A1,,{0,1,3,4,6})),2)&" and "&SMALL(N(OFFSET(A1,,{0,1,3,4,6})),3)

2] See attachment

Regards
Bosco

 

[LUNAglay]

Perhaps....

1] In I1, copied down :

=SMALL(N(OFFSET(A1,,{0,1,3,4,6})),1)&" , "&SMALL(N(OFFSET(A1,,{0,1,3,4,6})),2)&" and "&SMALL(N(OFFSET(A1,,{0,1,3,4,6})),3)

2] See attachment

Regards
Bosco

Dear Bosco,

You have resolve my problem and your help is greatly appreciated.

Thank you.