Software

Ask something
Discussion Locked

Excel help needed

0 helpful
8 replies

by jeremy the it guy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a table and a function. This table has 4 columns and 4 rows. Here are the columns:
16 24 36 48
16
24
36
48

That is my table. The information on the inside is populated by a formula. The columns and rows listed are the perimeter of the table and let's say at 36X36, I can produce 48 parts and it's the goal I'm trying to reach, I have a function that looks in to that table and looks for the "goal". What I need is I need to know which outer perimeter numbers make up that "goal". Here is the example:
16 24 36 48
16
24
36 48
48
Is there anyway to get 36 and 36 from 48? Is there a HLOOKUP or VLOOKUP I can use to get 36 and 36 just from finding 48? I know it's alot of info, if you would liketo get more involved, email me at: jwhitlock@starprecision.com. Thanks,

8 replies

by jeremy the it guy

The 36 in the last table is susposed to be under the 36 column. I just need to know how to reference a number by it's column and row. Thanks

by dklippert

I don't really grasp what you're trying to do.
A couple of other functions you might try, though, are Index(), Match(), and Offset()

by jeremy the it guy

I need to be able to do this:

16 24 36 48
16 1.6 1.7 1.8 1.9
24 2.8 1.6 2.4 3.2
36 3.6 7.5 2.1 4.6
48 4.1 12 4.6 4.9

There is a table where the first row and column are constant numbers representing the width and height. I have a cell that has a function looking for the max number in the table. In this case it's 12. From that, I have 2 more cells called Bx and By where Bx is a width and By is a height. I need to know how I can get 24 width and 48 height from 12. Do you understand anymore? If not, I'll try to explain it better. Thanks, Jeremy

by shanghai sam

Thanks for your help and sorry for the confusion. Here is what I need to do:

-- AA BB CC DD
EE 65 20 30 40
FF 50 60 70 80
GG 90 55 10 75
HH 95 85 45 35

I have a cell, not shown, on the same worksheet that hasa function for that cell to lookup the minimum number in the table I listed above. In this case, it would be 10. 10 is in the CC column and GG row, where CC and GG. AA BB CC DD are constant numbers representing width and EE FF GG HH are constantnumbers representing height. I have written a formula to take AA and EE and produce a number right under AA and beside EE. It will also do the same for the other columns and rows. What I need to do is be able to pick the smallest number in the table, and list which row and column it came from into another cell. I hope this explains things better. If not, email me at jwhitlock@starprecision.com with your phone number so I may call you. Thanks, Jeremy

by dklippert

Array enter the following formula in Cell G1. (Hit Ctrl+Shift+Enter).

=ADDRESS(MAX((B2:E5=G5)*ROW(B2:E5)),MAX((B2:E5=G5)*COLUMN(B2:E5)))

In H1 enter normally the following formula

=INDIRECT(LEFT(G1,2)&"1")&" by "& INDIRECT("A"&RIGHT(G1,2))
Substitute MIN to find the smallest.

This discussion is closed to new replies.