Assigning Data to a range of setpoints

Excel Forums Screenshot.PNG
Hi

I'm trying to organize some data into separate setpoints. So on the right I have 2 columns of data D_AVG_N and D_AVG_TQ in columns I and J respectively. In column K want to assign each row a setpoint in accordance with the table on the left. For example row 13 has a D_AVG_N of 1500 and a D_AVG_TQ of 80 so is at setpoint 5. I was trying to make an array formula with the index function but I cant seem to get it to work.

Thanks in advance.

Hi Ali

I've attached the actual sheet I'm working with. So The details tab is the one with the restrictions in and then column O is where I want the setpoints to go. So the setpoint should only depend on D_AVG_N and D_AVG_TQ. I've currently been using an if function just for now and you can see what it should look like in the column currently. I basically want it so that someone using the sheet could put any 3variables in the details table (so for example they might want the setpoint to be defined by EMB1_NOX_PPM, IM_AIR_T and CWC_WTRO_T instead of what it is currently.) and multiple setpoints with their given criteria and the setpoint column in the raw data tab will be assigned a number based on this criteria.

Thanks for your help

Andy

Hi Ali

Apologies I accidently uploaded an older worksheet, you'll find in this new one that D_AFIL_TQ is the header in the details tab instead of D_AVG_TQ. You should be able to find D_AFIL_TQ in column C in the raw data tab.

Answering your other question cells C4 and D4 and the ones below represent the minimum and maximum values that a variable can be for it to lay in that setpoint. So for example for row 4 in the raw data tab the D_AVG_N is a 1000 which is between 990 and 1010 and D_AFIL_TQ is 11.92 which is between 8 and 16 making it setpoint 1. So this row belongs to setpoint 1.

Thanks again

Andy

Ok so how the sheet will work is the raw data is data generated from a test. So columns A-N in the raw data tab are just text and number values, no functions. Then the thing I'm trying to make a function for is the set point column (column O) on the raw data tab. The details tab also contains no functions I just want it to be a table the sheet user can fill out themselves manually so they can define the criteria for each set point. So going back to the previous example, for row 4 in the raw data tab as the columns D_AVG_N and D_AFIL_TQ lie within the constraints for setpoint 1 which has been manually defined by the user I want cell O4 to be a 1 and then for row 14 for another example the D_AVG_N and D_AFIL_TQ lie within the constraints for setpoint 3 so I would like cell O14 to be a 3. I hope this makes sense, sorry for any confusion

Thanks

Is this what you need

Formula:

Please Login or Register  to view this content.

Try this

Formula:

Please Login or Register  to view this content.

where I4=1500, J4=80