• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match and Index Question | Offset maybe?

C

cubs610

Member
Hey Everyone..

Real quick I get questions from my co-workers since they've anointed me "the excel guy". Lucky. Me. Anyhow--- here is one sent to me... copied verbatim. Can interpret this a few ways... curious as to everyone else's thoughts. See below in italics

Can you give me an idea about how to find the "left hand" leg of a matrix...(can find intersecting cell from row and column with index and match), but if known value is from column head and also known value from inner matrix, how can you find the intersecting row value, ie the value of the left leg? Did that make sense?
FYI-most of tables were typical database, not a matrix...

Thoughts?

Thanks,
Dave
 
Hi Dave ,

What I think this means is that a table , for example , has row headers and column headers ; let us assume the table is in the range C7 through J30 , and the column headers are in row 7 , while the row headers are in column C.

Given the fact that the column referenced is column H , and the value is from cell H23 , how do we retrieve the row header C23 ?

Can you confirm whether this is what is being sought ?

Narayan
 
Hi All...

Thanks for the feedback. I'll check in with them to get a bit more clarity. Seems like I wasn't the only one. ;)

Stay tuned,
Dave
 
Hey Everyone,

Ugh.. I don't know if this is going to help, or hurt. Preferably help :) I heard from the client and here is the response I got (see Part 2). In order to make thing more legible. I'm going to condense everything into one post and upload a file that was sent. The content below is from their emails, labeled part 1 and part 2 in italics. Sorry guys, I don't mean to be a pain, just could use a hand.


Part 1 (email 1)
Can you give me an idea about how to find the "left hand" leg of a matrix...(can find intersecting cell from row and column with index and match), but if known value is from column head and also known value from inner matrix, how can you find the intersecting row value, ie the value of the left leg? Did that make sense?
FYI-most of tables were typical database, not a matrix...



Part 2 (email 2)
I have attached our Excel file which contains a matrix chart varying Relative Humidity and Temperature, which gives a corresponding value for grain moisture.
My question involves finding temp, given rel humidity and grain moisture equil.
I have a formula for moisture equil given temp and rel humidity as you can see from my sheet.
Any help you can give on this project would be wonderful...we are racing time as the warmer it gets the popcorn could mold without proper drying.

Thanks,
Dave
 

Attachments

  • WGH file for Dave C.xls
    271.5 KB · Views: 10
Hey Narayan,

Very cool. I appreciate your help! I'll download the modified file and take a peek.

Thank you very much!!!

-Dave
 
Narayan,

Question for you (or for all the other Excel sage's on this site). I took a look at the file you sent, and the formula, looking for some translation. At the risk of looking foolish, would you mind explain how this formula works? I'm not that great at formula interpretation.

=INDEX($E$5:$E$85,MIN(IF($B$31=OFFSET($E$5:$E$85,,MATCH($B$32,$F$4:$CH$4,0)),ROW($E$5:$E$85)-MIN(ROW($E$5:$E$85))+1)))

And yes, I did walk through the formula evaluator.... just need some more info :)

Thanks,
Dave
 
Hi cubs610,

Here is my attempt for your problem: Formula is below.

=INDEX($E$5:$E$85,MATCH($B$31,INDEX($F$5:$CH$85,,MATCH($B$32,$F$4:$CH$4,0)),-1))

This formula will return temp. based on Moisture & RH input.
Assumption made: RH will be inputed on exact basis. Moisture can be any value, the formula will return the exact temp. if the Moisture value is found in RH column or else it will return one higher temp.

Say for e.g. for RH - 15, Moisture - 8.09 it will return 43. But if I enter Moisture - 8.01, which is not there in RH 15 it will return an approximate value of 44 which is of 8.02.

Just check if this solution meet your criteria.

Regards,
 
Hi Dave ,

Basically there are a couple of basic building blocks there ; once you get the hang of these blocks , using them to develop a formula becomes that much easier.

The first building block is :

ROW($E$5:$E$85)-MIN(ROW($E$5:$E$85))+1

What this does is return a column vector i.e. an array with 1 column and 1 or more rows) of numbers , starting 1 and going up to what ever is the requirement. Here , our range of cells is E5 through E85 , which is 85 - 5 + 1 i.e. 81 rows.

The first component ROW($E$5:$E$85) gives us an array of numbers :

{5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85}

The next component subtracts the minimum row number ( which is 5 ) from the above array to give :

{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80}

Adding 1 to this gives us the final array of numbers from 1 through 81.

Next , let us look at what we are trying to do ; we have a matrix of values , with column headers in row 4 ( from F4 through CH4 ) , and row headers in column E , from row 5 through row 85.

We are given a value from somewhere in this matrix ; one assumption that I have made is that what ever value is given will have a match in the matrix. Since any value is the intersection of a column and a row , we have two unknowns ; we need to know either the column we are in or the row we are in.

In your case , the humidity , which is the column header is known , or at least can be found from the given RH value ; this is done by the next component :

OFFSET($E$5:$E$85,,MATCH($B$32,$F$4:$CH$4,0))

What this does is , it matches the given value of RH in B32 with the column headers in row 4 ; suppose the match value returns 72 ; this means that we need to look down the 72nd column from column E to see if there is a match for the moisture value.

This matching is done by the components :

MIN(IF($B$31=OFFSET($E$5:$E$85,,MATCH($B$32,$F$4:$CH$4,0)),ROW($E$5:$E$85)-MIN(ROW($E$5:$E$85))+1))

We compare each value in the desired column , with the entered moisture value , and see where we get the first match ; if we get a match , we take the row number of the match , normalize it to start from 1 , and then use this as an index to the temperature values in column E.

Narayan
 
@Somendra Misra and @ Narayan

Thank you both very much for your help!!! Especially Narayan, suffice to say you earned your 10th Dan when it comes to your Excel Ninja skills :)

'Preciate it!!

Best,
Dave
 
Hey guys... I thought I was done with this issue, but alas not so. The people I'm working with are wondering if this whole process (see above) can be automated into a macro. In truth I know zilch about VBA so like to hear what you guys think.

Thanks,
Dave
 
HI Dave ,

VBA can do anything that formulae can.

Can you specify what the code should do ?

Should it access the same cells for input , or should it prompt the user to enter values , and then display the result ?

How will you run the macro , through a button or through the user changing the input cells ?

Narayan
 
You must log in or register to reply here.
Back
Top