Formula help please

[lacogada]

Excel 2003


I receive PDF drawings that are drawn to scale, but are printed as such that scaling gets screwed up.


Looking for a formula to go into cell B9 ... that will reference cell I9,
and return 1/4".


... and when "Dimension from plan" is changed to 65 0 0 ( I9 will now be 5.33 )
B9 will now show 3/16"

 

[lacogada]

Unless theres a mathematical calculation that can do that - do you know why 5.333 should be 3/16 , is there a technical reason from your sheet ?

I'll try to explain how I got the numbers 4 and 5.33.

The blueprint had dimension of 65'-0"-0"
I measure that line in a PDF program and it gives the 12.19 measurement (B6, C6)


I have a draw program that allows different scalings.
I set the scale to 1" = 4' (1/4" scale)
That 65' dimension measured at 1/4" scale gave me the 48' dimension.
Changing the dimensions to inches and dividing gave me the 4.00 in I9.


In draw program I changed scale to .75" = 4' ( 3/16" scale )
But the draw program auto adjust the fraction of .75" to 1" and changes the 4' to 5.33 ft.
The draw program measures 65' dimension correctly at 3/16" scale.


That's how I know that the 4.00 would = 1/4" scale and 5.33 would = 3/16" scale.


Was trying to save time with spreadsheet of just taking the measurement in PDF program
and it would give me the scale ... without having to go to draw program and change different setting to find the correct scale.

 

[etaf]

ok, try this sheet
a lookup as suggested a few times now
the Number in I9 needs to be to 2 decimal places
So i have copied your IMAGE into the sheet
and Added the formula

so in I19 - change the number and you will see B19 change to a fraction

so I19 represents your I9 and B19 represents your B9

will this work !!!

https://www.dropbox.com/s/z7nknkostnd38s6/scale.xlsx?dl=0

 

[lacogada]

Thanks Wayne but cannot open the xlsx as I have ver 2003.

Ole, like me.

 

[etaf]

try
https://www.dropbox.com/s/hajyygrpebnw51b/scale_2003.xls?dl=0

 

[lacogada]

try
https://www.dropbox.com/s/hajyygrpebnw51b/scale_2003.xls?dl=0

Thanks for your time and patience ... I think this will surely help and I can put it to use.

May I ask how did you get the figures on the "lookup" sheet, as I see no formulas ?

 

[etaf]

copied and pasted from a website - then used a formula to work out the vlaues
http://www.sengpielaudio.com/calculatorLength.htm
gave the list - the reciprocal of decimal
divided into 1

 

[lacogada]

copied and pasted from a website - then used a formula to work out the vlaues
http://www.sengpielaudio.com/calculatorLength.htm
gave the list - the reciprocal of decimal
divided into 1

Thank you ... appreciate your help.

 

[Dave Patton]

With the lookup range sorted from smallest to largest, Lookup finds the decimal smallest not greater or equal.
N.B. E8 is rounded.

The rLookup is the lookup Wayne provided sorted small to large.
The 2 formulas shown do not require an exact match.

Excel 2010
	B	C	D	E	F	G
1
2	Feet	Inch	Fraction
3	48	8	0.75	584.75
4
5
6	12	0.19		146.28
7
8				4.00	1/4	1/4
9				5.33	3/16	3/16
10				5.32	13/64	13/64
11
2c

Cell Formulas
Range	Formula
E3	=B3*12+C3+D3
E6	=(B6+C6)*12
E8	=ROUND(E3/E6,2)
F8	=LOOKUP(E8,rLookup)
F9	=LOOKUP(E9,rLookup)
F10	=LOOKUP(E10,rLookup)
G8	=VLOOKUP(E8,rLookup,2,1)
G9	=VLOOKUP(E9,rLookup,2,1)
G10	=VLOOKUP(E10,rLookup,2,1)

Named Ranges
Name	Refers To	Cells
rLookup	='2c'!$K$2:$L$64

 

[lacogada]

Dave


Thanks for this but do not quite understand it and probably cannot get to it until next week.


Is 2c an added worksheet ?


If so, would K2 thru L64 be filled with the fractions Wayne provided ?


K2 thru L64 would is be defined range called rLookup ?

 

[Dave Patton]

2c is the sheet on my system where I prepared the example.

You can store the Lookup information where you want to store.