Find cell that meets criteria

**dudedude** · 07-07-2010, 11:18 PM

Hi,

I would like to know how I can find the cells that meet the following criteria in the attached spreadsheet. I want to find when there is a difference of between .93 and 1.03 both negative and positive between column C and column E and find the cells in colum A when this criteria has been met. So for example, cell A10 should be signaled out somehow (either highlighted or the other data could be parsed and just have the rows that meet the criteria or anything that would be faster than doing it manually). A21, A248, A266, A356 and A374 are the only returns that I would like. How can I achieve this?

Thank you for all your help.

**Paul** · 07-08-2010, 12:11 AM

Hi Dude,

When I sum the absolute values of the numbers in columns C and E for each row, I end up with about 15 rows that fall between .93 and 1.03 (including the rows you mentioned).

To show which rows were affected, I selected A4:A406 then applied the following conditional format to that range:

=AND(SUM(ABS(C4),ABS(E4))>=.93,SUM(ABS(C4),ABS(E4))<=1.03)

If that's not what you're after, please explain a bit more or re-word the question.

**dudedude** · 07-08-2010, 12:38 AM

Paul,

Thank you for the quick reply. I am sorry but I did not explain myself properly. I want to find when there is a difference of between .93 and 1.03 both negative and positive between column C and column E and find the cells in colum A when this criteria has been met. So I would actually have more entries than the ones I mentioned in my previous post.

I did manage to apply the conditional format to cells A4:a406 and added a highlight but it was not working.

I hope I am being clear this time.

Thanks.

**dudedude** · 07-08-2010, 12:42 AM

Paul,

Ok I figured out that I had to select the A4:A406 before and use the Current Selection and then everything works. But the math formula is wrong because now I need to find the difference and Excel does not have a SUBTRACT function. How can I adapt the formula you have generously written?

Thank you.

**Paul** · 07-08-2010, 01:26 AM

Ok, maybe this will work. I'm assuming by difference you then mean the absolute difference between the value in columns C and E in a given row. So if C4 is -.3 and E4 is .7, the difference is 1.0 which falls within the range.

If that's correct, then change your conditional formatting formula to:

=AND(MAX(C4,E4)-MIN(C4,E4)>=.93,MAX(C4,E4)-MIN(C4,E4)<=1.03)