Excel 2007 : IF AND Formula problem

[daniel_t]

[please see attached file]

Column H is to determine whether previous row contains the same data on cols D,E and F, and if G is not equal to 0 it should return a blank. It seems to have worked for row 3 but should also be blank for the rest of the rows but is returning an ‘x’.

Can anyone work out why this is happening?

Many thanks

[Azam Ali]

Hi daniel_t

your formula is giving correct result on the base of the criteria given in the formula

If the value is zero the and fucntion answer the False and it is choosing X

Kindly find the attatched file and inform what you need if there is both criteria are true or one is true and one is false

[daniel_t]

Thanks for reply Azam.

But shouldn't H4 give the same result as H3? Row 4 D-F is same as above row and G4 is '0'.
As per your True/False columns - if a row has both True it should return a blank, if either or both False should return 'x'.

Thanks

Dan

[arthurbr]

Your formula in H4being

=IF(AND($D4&$E4&$F4=$D3&$E3&$F3,$G3<>0),"","x")

and G3 NOT being <>0, the FALSE condition is the result

In H3 the part $G2<>0 is true because G2=1, so you get a blank

[daniel_t]

Thanks guys, I can see the glaring error I couldn't spot!

[arthurbr]

Tools - Formula Auditing - Evaluate Formulas

or Trace dependents/Precedents

Are a very good help for debugging formula