Table double axis HELP PLEASE!!!

[nixpuri]

Hi!

So I have a table with two axes, the Y-axis is the price of a stock and the X-axis is the number of stocks to be bought.
As the number of the stocks bought increases, the price isn't linear, and changes arbitrarily.

Basically, this table has all the prices of the various stocks based on their quantity.

I have a separate areas, Cells A1-C1. In A1, I want to put the price, in B1, I want to put the quantity of stocks bought, and I want C1 to automatically use those coordinates on the table, and automatically fill in the value for the inputted A1 and B1 coordinates.

Is this possible?

The numbers are all already there, I just want to make it easier for someone by just putting in the X-axis and Y-axis values instead of having to look at the complex table itself.

Thanks!

Nix

 

[inactiveuserps07]

Yes, this can be easily done with INDEX and MATCH, if I'm understanding you correctly. What is the range of your stock table? And if the table is named, what is the name of the table?

 

[nixpuri]

Hi Erik!

So on my y-axis, I have the Price of each stock, as follows:

0.25
0.50
0.75
1.00
1.25
1.50
1.75
2.00
2.25
2.50
2.75
3.00
3.25
3.50
3.75
4.00
4.25
4.50
4.75
5.00
6.00
7.00
8.00
9.00
10.00 and x-axis being the number of stocks as follows: 15, 20, 25, 50, 75, 100

<colgroup><col></colgroup><tbody>
</tbody>

 

[nixpuri]

The table isn't named, but the X-axis is "Contracts" and the Y-axis is "Option Price", but the table I won't even have visible for anyone except myself.

 

[inactiveuserps07]

nixpuri, it's always a good idea to create an actual table of such data, so that you can expand or contract it later without creating #REF problems.

However, if you don't want to name the table and are you you won't move it from where it is right now, what is the cell range of the data? Ex: A5:Z100, etc.

Which column contains your Y-axis values?

Which row contains your X-axis values?

 

[nixpuri]

U3-U27 is my Y-axis column, 0.25-10.00.
V2-AA2 is my X-axis column, 15-100.

Does this help?

 

[inactiveuserps07]

nixpuri, yes that is what I needed to know, thanks.

Let me help you set things up correctly, so that it will be easy for your users.

1. If Column V is not already formatted as currency, we'll want to do that. Click the "V" at the top of that column so that the whole column is highlighted, right-click, choose "Format Cells" from the menu that appears, click the "Number" tab, and choose "Currency."

2. Click the "1" beside Row 1 so that the whole row is highlighted. From the Home tab, increase the font size to 12, click "B" for bold, and choose "Center" for alignment.

3. Enter the following headers into cells A1, B1 and D1 respectively: NUMBER OF STOCKS____PRICE____RESULT

4. Hold down the Ctrl key and click cells A2, B2 and D2 (not C2). Release the mouse. Right-click, choose "Format Cells," click the "Border" tab, click both "Outside" and "Inside", then click "OK."

5. Click into cell A2. Open the Data tab in the ribbon and click "Data Validation." From the dropdown list, choose "Data Validation" again. In the window that appears, under "Allow:" choose "List." Make sure "Ignore blanks" and "In-cell dropdown" are checked. In the box for "Source:" enter this formula: =V3:AA3 then click "OK."

6. Click into cell B2. Open the Data tab in the ribbon and click "Data Validation." From the dropdown list, choose "Data Validation" again. In the window that appears, under "Allow:" choose "List." Make sure "Ignore blanks" and "In-cell dropdown" are checked. In the box for "Source:" enter this formula: =U4:U28 then click "OK."

7. Click into cell C2 and enter this formula: =INDEX(U3:AA28,MATCH(B2,U3:U28,0),MATCH(A2,U3:AA3,0))

8. Hold down the Ctrl key and click cells A2 and B2, then release. Right-click, choose "Format Cells," click the "Protection" tab, uncheck "Locked" and click "OK."

9. When you are ready for your users to begin using the sheet, open the Review tab in the ribbon and click "Protect Sheet." You probably don't need a password; it's just to keep casual users from accidentally erasing formulas. Click "OK."

If you need to make changes after that, just click the Review tab again and then click "Unprotect Sheet."