Need a multi-step logical test

[Mike Morris]

I need help with a logical test.

I have a table of data with the following columns A through E:


item_no item_desc_1 item_desc_2 avg_cost last_cost

The table consists of 49, 061 rows.
There are long sets of item numbers that differ only in the last 3-5 digits but have the same text in "item_desc_1". (i.e.: the same item in different colors)

I'm looking for items that have the same description but have average costs that are drastically different.
I'm using as a rough checkpoint cost differentials of +/- 100%; if a given item is more than twice or less than half the cost of the same item in another color, I want to flag it for review.

I tried the following logical test in excel, but it failed:

IF(B3=B2 AND D3>D2*2 OR D3<D2*0.5),"Check Me","within margin")

I realize now that I need to say something like

if B3=B2, then perform test If D3>D2*2 OR D3<D2*0.5, else do nothing

then say

IF D3>D2*2 OR D3<D2*0.5),"Check Me","within margin"

but I'm not sure how to do that.

[Ace_XL]

Perhaps this is what you are after
=IF(AND(B3=B2,OR(D3>D2*2,D3<D2*0.5)),"Check Me","within margin")