software

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: www.excelforum.com/member.php...oamp;userid=31623
View this thread: www.excelforum.com/showthread...hreadid=522444Hi!

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

gt; 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 quot;keyedquot; to the vertical axis no matter if
it's height or width?
The max value entered is ALWAYS quot;keyedquot; 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

quot;pdgoodquot; gt; wrote in
message news
gt;
gt; I'm hoping that this is much simpler to do than it is to explain. I
gt; attempted to upload the .xls file but got an invalid file message.
gt; I'm looking for a formula to find the intersection on a graph. The user
gt; will input a width and a height and those numbers need to be rounded up
gt; to the nearest .25 to match the axis of the graph. (This can be a
gt; separate formula if that makes it any easier). Then the largest of
gt; those numbers should be referenced to the horizontal axis (Row 1) and
gt; the smaller of those numbers to the vertical axis (Column A) of the
gt; graph and the intersection be found.
gt; Example:
gt; On sheet 1, B4=height, B3=width. These are filled out by the user. If
gt; for example they enter 2.339 in A1 (or anything above 2.25 but less
gt; than or equal to 2.50) it would be rounded up to 2.50. Then if they
gt; enter a lower number - let's say 1 - in B1, then the formula would
gt; lookup both numbers on Sheet 2 and reference the lower number (1) to
gt; the vertical axis and the higher number (2.5) to the horizontal axis
gt; and find the intersection.
gt;
gt; Sheet 2 is titled Engraving Scale and is labeled with Row 1 being the
gt; horizontal axis as follows:
gt; A1=0
gt; B1=.25
gt; C1=.5
gt; D1=.75
gt; E1=1
gt; F1=1.25
gt; etc thru CK1=22
gt;
gt; Column A is the vertical axis and is as follows:
gt; A69=0
gt; A68=.25
gt; A67-.5
gt; A66=.75
gt; A65=1
gt; A64=1.25
gt; etc thru A2=16.75
gt;
gt; In every column from Row 2 through Row 68 are numbers that are to be
gt; referenced by the intersection of the height and width.
gt;
gt; Thanks so much. You guys work miracles!
gt; Please let me know if I've not explained myself well.
gt;
gt;
gt; --
gt; pdgood
gt; ------------------------------------------------------------------------
gt; pdgood's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31623
gt; View this thread: www.excelforum.com/showthread...hreadid=522444
gt;

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: www.excelforum.com/member.php...oamp;userid=31623
View this thread: www.excelforum.com/showthread...hreadid=522444Ok, just post back to this thread if you need further assistance.

Biff

quot;pdgoodquot; gt; wrote in
message news
gt;
gt; You are right, the first comment is an error on my part. I moved the
gt; entry cells and forgot to update this question. (I've been working on
gt; how to word it for several days). I can make them be any cell, so I'll
gt; go with your example.
gt;
gt; Yes, the min value entered is always keyed to the vertical axis no
gt; matter if it's the height or width and the max value to the
gt; horizontal.
gt;
gt; You are right, A1 is not actually used since the value of 0 would never
gt; be applicable in this chart. I added it because I thought it would help
gt; clarify the numbering system better (rather than beginning on .25) ,
gt; but I see now that I just muddied my explanation rather than cleared it
gt; up. Glad you were able to see through this.
gt;
gt; I'm anxious to try the formula you suggest and will as soon as I return
gt; to work tomorrow. If I struggle I will upload the file as you suggest,
gt; but your formula looks very promising. Many thanks.
gt;
gt;
gt; --
gt; pdgood
gt; ------------------------------------------------------------------------
gt; pdgood's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31623
gt; View this thread: www.excelforum.com/showthread...hreadid=522444
gt;