Excel: Need Help

I'm somewhat unfamiliar with the functions and commands in MS Excel and need some help here.

I need to figure out how I can set it up to where, in a table on the right, if I type in the a number in the range of the X-axis (Say the number is 45), then I also type in the letter of the Y-axis (for this example, Y=C), then I need it to calculate the cooralating number of the two (in this case, $35). (This is for work so these numbers are purely just an example. When I can calculate out how to format it, these numbers will be replaced with others)

See image>>>

***EDIT*** Image removed. See image in Post #19

 

Log in or Sign up to hide all adverts.

Just to be picky...
At least 1 but less than 10
at least 11 but less than 20

Where does 10 go?

 

Log in or Sign up to hide all adverts.

Oops. Well, just use your imagination. lol

 

Log in or Sign up to hide all adverts.

In Excel 2010 it can be done with a swing table. In previous versions you might need to use VB macros.

 

Um.... ok. Imagine you're teaching a 5 year old how to do this. lol I don't know what either of those are.

 

MZ, what do you mean "45"? There is no number 45 on the x-axis. Or do you mean that the formula has to figure out which row to go to?

You could use something crude like this:

=if(or(isblank(X);isblank(Y));"";index(RANGE;left(X-1;1)+1;code(Y)-64))

 

Not only does the formula have to figure out which row (x-axis) but also what the end number is by also using the specified column (y-axis).

So, I want a table that gives the option to TYPE in a number (say, 45), and have it automatically tie to the appropriate range. Furthermore, I also want to type in the Y-axis identifier (A,B or C etc) and then have it automatically populate the $$ number - the cross of the two axis'.

So, if I type in 45 in field #1 and then C in field #2, I want field #3 to give me the cross of the two - in this case $35

 

The formula above will do that. It is a bit crude though.

Edit: the "+1" part should by modified depending on on which row in excel you range begins. If it starts on row 2 then type +1, 3 type +2, etc.

Oh, X and Y are cell references of course.

 

Not working.

 

ok Let me try. brb

 

Hang on I'll post a pic. The formula is kind of specific so it may not work on your table.

 

Still on luck. It's not accepting the formula.

 

Please Register or Log in to view the hidden image!

 

Hmm.. if the real numbers you are going to use are not going to be 1-10, 11-20, etc this formula is not going to work of course.

Do you see the formula's tab? Click that, then click insert function. It's pretty easy to figure out by yourself.

 

That was obviously bullshit.. lol No idea why I told you that

Please Register or Log in to view the hidden image!

MZ, if you just give me the real numbers etc I will make it for you.

 

Enmos, not sure I understand the logic of this: =LEFT(S2-1,1)+1

How is that supposed to pick out the correct row based on the value entered in S2, when the values of S2 are between 2 and 9?

 

Ok, this one is universal as long as you keep categories of ten (1-10, 11-20, etc):

INDEX(C2:J18;TRUNC((L6-1)/10)+1;MATCH(M6;C1:J1))

C2:J18 is your range.
C1:J1 is the range of the "Y-axis" (A, B, C, etc).
L6 is the cell where you input the X-value.
M6 is the cell where you input the Y-value.
Change any of these if yours are different.

 

I was not sure what the error was in his example. In fact, I'm still not sure.
The way I see it S2's range is 1-10 or 1-9.

 

Here, let's try this...

Please Register or Log in to view the hidden image!

I want to be able to type any number in that Qty field, any color in the Color field and have it give me the $ amount.

 

Ok, for categories of 9 it's this:

INDEX(C2:J18;IF(L6/9>TRUNC(L6/9);TRUNC(L6/9)+1;TRUNC(L6/9));MATCH(M6;C1:J1))