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

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

Where does 10 go?

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

Where does 10 go?

Oops. Well, just use your imagination. lol

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

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))

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?

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

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.

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 working.

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.

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.

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.

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

http://www.enmos.eu/temp/Afbeelding1.jpg

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.

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

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

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.

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?

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...

http://img338.imageshack.us/img338/3910/captureuo.jpg

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))

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.

Try your spreadsheet with a 2 in it.

My version, as in your example is ok for values from the first row if a 1 is entered, but not 2 through 10, after that it is ok.

Here, let's try this...

http://img338.imageshack.us/img338/3910/captureuo.jpg

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.

Yes, but that isn't going to work, because you have multiples of the same value in one row there.

The last formula I posted should work for your example in post #1.

Try your spreadsheet with a 2 in it.

My version, as in your example is ok for values from the first row if a 1 is entered, but not 2 through 10, after that it is ok.

This one works for the example in the OP (categories of 9: 1-9, 10-18, 19-27, etc.):

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

Ah, I missed that when I posted.

Looks good.

I was still scratching my head on this when you posted your answer, and I'm normally pretty good at Excel, but your skill is impressive Enmos.

Note, not sure if this is causing him problems, but in my version of Excel I have to use commas where you use semicolons.

Image:

http://www.enmos.eu/temp/cat9.jpg

Ah, I missed that when I posted.

Looks good.

I was still scratching my head on this when you posted your answer, and I'm normally pretty good at Excel, but your skill is impressive Enmos.
Heh, thanks. I'm just messing around really :)


Note, not sure if this is causing him problems, but in my version of Excel I have to use commas where you use semicolons.
Hmm.. that could very well be it. Thanks!

Hmm.. I just noticed what could be a flaw in Killjoyklowns example.
In his example the 'x-value' 10 does not exist. Same with 20, 30, 40, etc. Or was that intentional?

IMO, it should be

at least 1 - less than 10
at least 10 - less than 20
etc.

How about a more generic formula, where the values in column A and B don't have to be in multiples of any given number?

say:

1 - 25
26 - 45
46 - 89
90 - 120
121 - 500

How about a more generic formula, where the values in column A and B don't have to be in multiples of any given number?

say:

1 - 25
26 - 45
46 - 89
90 - 120
121 - 500

Well, yeah, that should be doable as well. But I've got to run though. I'm late for an appointment as it is :o

Hmm.. I just noticed what could be a flaw in Killjoyklowns example.
In his example the 'x-value' 10 does not exist. Same with 20, 30, 40, etc. Or was that intentional?

IMO, it should be

at least 1 - less than 10
at least 10 - less than 20
etc.

What Killjoyklown example?????

Should be something like this though:

IF(L6/"less than-1">TRUNC(L6/"less than-1");TRUNC(L6/"less than-1")+1;TRUNC(L6/"less than-1"))

What Killjoyklown example?????

lol in the OP.

Well, yeah, that should be doable as well. But I've got to run though. I'm late for an appointment as it is :o

Sorry, the end product will not have patterned numbers. They are pretty random. I need a formula that can identify the contents of the cell, not match it by math.

Sorry, the end product will not have patterned numbers. They are pretty random. I need a formula that can identify the contents of the cell, not match it by math.

Yeah, sorry that would work. I was pretty tired before. Still am actually..
This is the universal formula:

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

B2 represents the category you want to use (it simply takes it from the sheet from coordinate B2).
It doesn't matter what categories you are using and it doesn't matter if you are using random numbers.
It also doesn't matter whether you are using A, B, C, etc or color names or whatever for the Y-axis.

Image:

http://www.enmos.eu/temp/kjksheet.jpg

And here is the sheet itself (I hope it will load for you since I use a Dutch version):
http://www.enmos.eu/temp/kjksheet.xls


Edit: If you have any further questions, please ask :)

What Killjoyklown example?????

Oh damn.. I really shouldn't be on here in this state :D
I meant MZ and called him killjoyklown... :o

MZ, please ignore the filenames and stuff.. lol

Off to bed then.

Simplify your table, get rid of the second column, it's un-neccessary.

Then use this formula, which is based on the This table:
http://sciforums.com/picture.php?albumid=64&pictureid=999

=INDEX(B2:K21,MATCH(VLOOKUP(N5,A2:A21,1,TRUE),A2:A 21,0),MATCH(N4,B1:K1,0))

This formula is universal.

To put this in perspective, I recently had to write a spreadsheet that not only had to do this (among other things), but first had to determine, based on user inputs, which of the 50 reference tables to look at. each reference table was a unique combination of two variables.

Oops - the cell labled 'At least', directly below colour, should be labled 'Order Quantity'.

mea culpa.

If anybody is interested, here's the "front end" for the spread sheet I mentioned.
http://sciforums.com/picture.php?albumid=64&pictureid=1000

It's for calculating whether or not the seperation between septic tanks and drinking water wells is adequate, based on viral transport guidlines.

The stuff hilighted in yellow are outputs, everything else is entered in by the user, however, the values available in the pulldown menus labled "Seperation" and "Nearest Table Depth" are actually dynamic - they change depending on the combination of Vadose Zone and Saturated Zone you're looking at.

Simplify your table, get rid of the second column, it's un-neccessary.

But he wants the formula to figure out which row to go to using a value between two category values.
He wants the 'at least' column ;)

But he wants the formula to figure out which row to go to using a value between two category values.
He wants the 'at least' column ;)

You missed the point - the flag 'True' tells the VLOOKUP function to look for the nearest value in the specified list rather than the exact match. If the list is ordered eg:
5
10
15
20

and I put in (for example) 12, then use the flag 'TRUE' as I showed in my formula, VLOOKUP (and HLOOKUP) will return the nearest match, rather than the exact match.

It doesn't matter if you enter in 10, 11, 12, 13, or 14 - in all cases, VLOOKUP will return '10' (or 15 for any number>=15 and <20) as the nearest result in your list - as long as the flag to return the nearest match, (rather than the exact match) is set to 'TRUE'. You don't need to specify the full range as has been done so far, you only need to specify the lowest value in each bin - IE 'At least' or 'minimum order'. Hence, the second column listed - the 'less than' column - column B is completely un-neccessary for finding the right row.

Yes, but if you enter '9', it will return '10' as well.

No, I don't believe it does.

No, I don't believe it does.

Well 10 is the nearest number to 9. I don't know, perhaps you're right. I haven't tried VLOOKUP, so..

Well 10 is the nearest number to 9. I don't know, perhaps you're right. I haven't tried VLOOKUP, so..

No perhaps about it ;)

I am right.

http://sciforums.com/picture.php?albumid=64&pictureid=1003

Yes, you're right. I just tried it :o

Trippy, since you seem to know a lot about Excel, how do you prevent a range from automatically changing when you drag out a cell with a formula in it that contains a range?