Find nearest value

[Guest]

I have a column with diameters in thousands of an inch.
I need to find the nearest equivalent in fractions such as 1/64 1/128. The
fractions show as decimals in another column.

The fractions format only goes to 1/16 which is not small enough.
The match function gives an error.
Many thanks

 

[Guest]

Format the data as Fractions > Up to three digits

 

[Guest]

Formating the data as Fractions > Up to three digits
does not help.

I need fractions in this format:
1/2^1; 1/2^2; 1/2^3; 1/2^4; 1/2^5; 1/2^6; 1/2^7;
i.e. Imperial measurements.

 

[Dana DeLouis]

Perhaps if you wish to work with an accuracy of say 1/(2^16) (1 / 65536)
then maybe ...
Numerator: =ROUND(A1*65536,0)
Denominator: 65536

 

[Herbert Seidenberg]

For denominators 2, 4, 8, 16, 32, 64 and 128,
depending on the closest match, as in these examples:

0.476823 61/128
0.513072 33/64
0.158690 5/32
0.689468 11/16
0.877029 7/8
0.746163 3/4
0.501050 1/2

Insert > Name > Define
input Refers to: 0.476823
bse Refers to: =2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1)
mult Refers to: =ROUND(input*bse1,0)/bse1
err Refers to: =ABS(input-mult)
merr Refers to: =MIN(err)

Paste this array formula into a cell next to input:
=INDEX(mult,MATCH(merr,err,0))
and Format > Cells > Fraction > Up to 3 digits

 

[Guest]

Your example is indeed what I need. However, I could not
make the formula work.
I pasted the fractions in A8 to A14. The formula substitutions
gave me:
=INDEX(ROUND(.47682*2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1),0)/2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1),MATCH(min(ABS(a1-ROUND(input*2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1),0)/2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1)),0))
which gave me the error Too few arguments.
Would it be simpler to place the 2^x fractions in a different column?

 

[Herbert Seidenberg]

Your substitution formula contains several errors.
Although I don't recommend proceeding this way,
here is the correct formula:
=INDEX(ROUND(input*2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1),0)
/2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1),
MATCH(MIN(ABS(input-ROUND(input*2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1),0)
/2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1))),
ABS(input-ROUND(input*2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1),0)
/2^(ROW(INDEX(A:A,1):INDEX(A:A,8))-1)),0))
Substitute input with A8
otherwise you will get a #NAME? error.
Enter with CNTRL+SHIFT+ENTER

I recommend instead that you type the header
input
in cell A7,then select A7:A8 and
Insert > Name > Create > Top Row
Q: Replace existing definition of input? A: Yes
then put my original array formula
=INDEX(mult,MATCH(merr,err,0))
into B8. Enter it with CNTRL+SHIFT+ENTER
instead of just ENTER.
If you get the correct answer, go to
Insert > Name > Define
and examine how input is now defined.
If you have problems giving names to the other values,
post again.

 

[Guest]

After correcting the formula I got the correct results.
Thank you.
Excel General Questions reply windows (unlike Excel Worksheet
functions or Excel programming) do not show
the rating bar at the bottom. How do I rate the reply?