Cell Referencing Question

**Impartial Derivative** · 06-28-2011, 01:07 PM

Hey, I have a workbook with data sorted by frame number and I'm currently in the process of writing a macro to do certain calculations utilizing the standard deviation. My next step is to incorporate a calculation that eliminates outliers from my data set. In Column H, I am looking to have a logic formula, that states the following:

=IF(AND((L4-M4)<=G4,(L4+M4)>=G4),G4,"")

However, Columns L & M contain ranges of variable size and are sorted based off the frame number displayed in column K. My goal is to have a code that reads the value of the active cell in Range A1:A and then searchs for this same number in Column K and once it finds that cell references the data in L and M for my logic statement. Any suggestions on how to do this would be greatly appreciated as I am a little lost on how/if this can be accomplished. I've attached a workbook to hopefully clarify the problem.

**Marcol** · 06-28-2011, 02:42 PM

Try this
In K3

Please Login or Register  to view this content.

Drag/Fill Down

In L3

Please Login or Register  to view this content.

Drag/Fill Down

In M3 this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter
Drag/Fill Down

Does this help?

**Impartial Derivative** · 06-29-2011, 11:53 AM

Marcol,

Your post was indeed useful, however I am still running into a problem with my formula for Column H. I need to find a way for the macro to recognize the frame number associated with a cell and match it with the data from L and M in order to see gait my data. As of right now the formula works for the first block of data but then fails when you fill down and certain cells are looking to reference empty cells for the average distance and standard deviation.