Find nearest value
 

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
--
Occasionally stumped

Find nearest value
 

Format the data as Fractions Up to three digits
--
Gary's Student


"Two-Canucks" wrote:

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
--
Occasionally stumped

Find nearest value
 

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.
--
Occasionally stumped


"Gary''s Student" wrote:

Format the data as Fractions Up to three digits
--
Gary's Student


"Two-Canucks" wrote:

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
--
Occasionally stumped

Find nearest value
 

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

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Two-Canucks" wrote in message
...
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.
--
Occasionally stumped


"Gary''s Student" wrote:

Format the data as Fractions Up to three digits
--
Gary's Student


"Two-Canucks" wrote:

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
--
Occasionally stumped

Find nearest value
 

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

Find nearest value
 

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?
--
Occasionally stumped


"Herbert Seidenberg" wrote:

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

Find nearest value
 

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.

Find nearest value
 

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?
--
Occasionally stumped


"Herbert Seidenberg" wrote:

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.