lowest via highest nums!

[stewart08]

hi excelers, I will try to make this question clear:

how does one find the lowest or highest numbers from other numbers i.e.

(highest num) "25"
(lowest num) "5"

which number 6 or 20 match closest to the two numbers above!

should result in = "6" as its the closet by digits to lowest num 5.
but 20 is not chosen as its 5 digits away from 25.

hope this is clear

[mrice]

Try the attached. There's a user defined function called Nearest used in cell E2 which takes 4 arguments. You can see the code by hitting Alt F11 and looking at module1.

[stewart08]

thanks for the reply mrice.

can this be do without visual basic code as I donot want to get into code at this point.

thanks

[mikerickson]

If your lowest number is in A1 and your highest number in B1,

=IF(MIN(ABS(A1-D1),ABS(B1-D1)) < MIN(ABS(A1-C1),ABS(B1-C1)), D1, C1)

will select between C1 and D1, the number that is closest to the boundry values.

[stewart08]

thanks again mikerickson

this forum is amazing very fast reply too....