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"

 

[etaf]

what is the list in i9 for b9
i9 = 4.00 . b9 = 1/4
i9 = 5.33 b9 = 3/16

you can use a lookup list

have a reference table with the values for i9 and what b9 should be
then use vlookup()

 

[lacogada]

Was hoping for a formula ... but thanks for the reply.

 

[etaf]

i can give a formula
but need to know what you want to do as i asked
so i can work out what formula would work!!!

as i asked

what is the list in i9 for b9
i9 = 4.00 . b9 = 1/4
i9 = 5.33 b9 = 3/16

 

[lacogada]

i can give a formula
but need to know what you want to do as i asked
so i can work out what formula would work!!!

Sorry ... I'll try to answer or explain.


Cells B3, C3, D3 is the actual dimension from blueprint.
Cells F3, G3, H3 is dimension from blueprint in inches.
Cell I3 is sum of F3, G3, H3.


I open the bluprint in my PDF reader and use it's measuring tool.


Cell B6, C6 is the reading from the PDF reader measuring tool.
Cell I6 is sum of B6, C6.


Cell I9 is I3 / I6.


I just mentioned that the number 4.00 in cell I9 is = to 1/4" scale.


Also if the dimension in B3, C3, D3 is changed to 65'0"0"
I9 will read as 5.33 which would make it 3/16" scale.


I'm not particularly looking for an answer of 1/4" or 3/16" ....
it will be what ever the formula gives, 1/8", 3/32", 3/8", etc..


Thank you.

 

[Dave Patton]

Excel 2010
	B	C	D	E
2	Feet	Inch	Fraction
3	48	8	0.75	584.75
4
5
6	12	0.19		146.28
7
8				4.00
2c

Cell Formulas
Range	Formula
E3	=B3*12+C3+D3
E6	=(B6+C6)*12
E8	=E3/E6

 

[etaf]

I just mentioned that the number 4.00 in cell I9 is = to 1/4" scale.


Also if the dimension in B3, C3, D3 is changed to 65'0"0"
I9 will read as 5.33 which would make it 3/16" scale.


I'm not particularly looking for an answer of 1/4" or 3/16" ....
it will be what ever the formula gives, 1/8", 3/32", 3/8", etc..

4 = 1/4
5.33 = 3/16
?? = 1/8
?? = 3/32
?? = 3/8
etc = ???

i would need to know all of the possible List to be able to use a lookup
how does excel know that 5.33 should show 3/16 in B9

then if that list was put on Sheet2 Columns A and B
you could use in B9
=vlookup(I9,sheet2!A:B,2,false)
and B9 will change to the value in Sheet2!B based on I9 looked up in sheet2!A
hope that helps

 

[lacogada]

Thanks Dave

 

[lacogada]

how does excel know that 5.33 should show 3/16 in B9

That's what I was hoping your formula could tell me

Thanks ... I'll try and find the answers.

 

[etaf]

if you create a list of numbers to fractions then excel will use the list and we can setup a forumula , as i explained in previous post
otherwise , there's no formula i know to change 5.333 into a fraction of 3/16 - why would excel know that 5.333 should be 3/16th same as why will excel know that 4 is 1/4
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 ?