I'm hoping that this is much simpler to do than it is to explain. I
attempted to upload the .xls file but got an invalid file message.
I'm looking for a formula to find the intersection on a graph. The user
will input a width and a height and those numbers need to be rounded up
to the nearest .25 to match the axis of the graph. (This can be a
separate formula if that makes it any easier). Then the largest of
those numbers should be referenced to the horizontal axis (Row 1) and
the smaller of those numbers to the vertical axis (Column A) of the
graph and the intersection be found.
Example:
On sheet 1, B4=height, B3=width. These are filled out by the user. If
for example they enter 2.339 in A1 (or anything above 2.25 but less
than or equal to 2.50) it would be rounded up to 2.50. Then if they
enter a lower number - let's say 1 - in B1, then the formula would
lookup both numbers on Sheet 2 and reference the lower number (1) to
the vertical axis and the higher number (2.5) to the horizontal axis
and find the intersection.

Sheet 2 is titled Engraving Scale and is labeled with Row 1 being the
horizontal axis as follows:
A1=0
B1=.25
C1=.5
D1=.75
E1=1
F1=1.25
etc thru CK1=22

Column A is the vertical axis and is as follows:
A69=0
A68=.25
A67-.5
A66=.75
A65=1
A64=1.25
etc thru A2=16.75

In every column from Row 2 through Row 68 are numbers that are to be
referenced by the intersection of the height and width.

Thanks so much. You guys work miracles!
Please let me know if I've not explained myself well.


--
pdgood
------------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=522444

Hi!

Some things don't make sense (to me).

On sheet 1, B4=height, B3=width

But then you say the users enter values in A1 and B1. So, what's B3 and B4
got to do with it?

The min value entered is ALWAYS "keyed" to the vertical axis no matter if
it's height or width?
The max value entered is ALWAYS "keyed" to the horizontal axis no matter if
it's height or width?

Also, you say the table starts in cell A1 = 0. Normally, the table headers
would start in B1 and A2 with A1 not being part of the actual table.

Ok.......

It'd be easier to use cells to round the input values:

A1 = 2.339
B1 = 1

A2 = formula:

=CEILING(A1,0.25)

Copy over to B2

So,

A2 = 2.5
B2 = 1

Return the intersecting value:

=INDEX(Scale!B2:CK69,MATCH(MIN(A2:B2),Scale!A2:A69 ,0),MATCH(MAX(A2:B2),Scale!B1:CK1,0))

If this is way off you can upload your sample file, just zip it or rename it
and give it a *.txt file extension.

Biff

"pdgood" wrote in
message ...

I'm hoping that this is much simpler to do than it is to explain. I
attempted to upload the .xls file but got an invalid file message.
I'm looking for a formula to find the intersection on a graph. The user
will input a width and a height and those numbers need to be rounded up
to the nearest .25 to match the axis of the graph. (This can be a
separate formula if that makes it any easier). Then the largest of
those numbers should be referenced to the horizontal axis (Row 1) and
the smaller of those numbers to the vertical axis (Column A) of the
graph and the intersection be found.
Example:
On sheet 1, B4=height, B3=width. These are filled out by the user. If
for example they enter 2.339 in A1 (or anything above 2.25 but less
than or equal to 2.50) it would be rounded up to 2.50. Then if they
enter a lower number - let's say 1 - in B1, then the formula would
lookup both numbers on Sheet 2 and reference the lower number (1) to
the vertical axis and the higher number (2.5) to the horizontal axis
and find the intersection.

Sheet 2 is titled Engraving Scale and is labeled with Row 1 being the
horizontal axis as follows:
A1=0
B1=.25
C1=.5
D1=.75
E1=1
F1=1.25
etc thru CK1=22

Column A is the vertical axis and is as follows:
A69=0
A68=.25
A67-.5
A66=.75
A65=1
A64=1.25
etc thru A2=16.75

In every column from Row 2 through Row 68 are numbers that are to be
referenced by the intersection of the height and width.

Thanks so much. You guys work miracles!
Please let me know if I've not explained myself well.


--
pdgood
------------------------------------------------------------------------
pdgood's Profile:
http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=522444

You are right, the first comment is an error on my part. I moved the
entry cells and forgot to update this question. (I've been working on
how to word it for several days). I can make them be any cell, so I'll
go with your example.

Yes, the min value entered is always keyed to the vertical axis no
matter if it's the height or width and the max value to the
horizontal.

You are right, A1 is not actually used since the value of 0 would never
be applicable in this chart. I added it because I thought it would help
clarify the numbering system better (rather than beginning on .25) ,
but I see now that I just muddied my explanation rather than cleared it
up. Glad you were able to see through this.

I'm anxious to try the formula you suggest and will as soon as I return
to work tomorrow. If I struggle I will upload the file as you suggest,
but your formula looks very promising. Many thanks.


--
pdgood
------------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=522444

Ok, just post back to this thread if you need further assistance.

Biff

"pdgood" wrote in
message ...

You are right, the first comment is an error on my part. I moved the
entry cells and forgot to update this question. (I've been working on
how to word it for several days). I can make them be any cell, so I'll
go with your example.

Yes, the min value entered is always keyed to the vertical axis no
matter if it's the height or width and the max value to the
horizontal.

You are right, A1 is not actually used since the value of 0 would never
be applicable in this chart. I added it because I thought it would help
clarify the numbering system better (rather than beginning on .25) ,
but I see now that I just muddied my explanation rather than cleared it
up. Glad you were able to see through this.

I'm anxious to try the formula you suggest and will as soon as I return
to work tomorrow. If I struggle I will upload the file as you suggest,
but your formula looks very promising. Many thanks.


--
pdgood
------------------------------------------------------------------------
pdgood's Profile:
http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=522444