If error formula

[geliedee]

Hi All,

Just wondering how can I put this condition into formula?

IF Account Group = YSTP
AND Sales Organization <> IT*
AND Distribution Channel = 14 only
AND Division = 02, 03, 05, 06, 12 only (no other Sales Area)
WHERE Condition Group 1 <> 04
THEN FAILED

IS MY FORMULA BELOW CORRECT?

Please Login or Register  to view this content.

[samba_ravi]

=IF(AND(I5="YSTP",E5<>"IT",F5&""="14",OR(G5={"02","03","05","06","12"}),H5<>4),"PASSED","FAIL")
try this
if not worked attach sample excel file

Please Login or Register  to view this content.

[Glenn Kennedy]

Try this...
=IF(AND(I5="YSTP",E5<>"IT",F5=14,H5<>4,OR(G5={"02","03","05","06","12"})),"PASSED","FAIL")

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

take care with F5. Is it a numeric or string?

[Glenn Kennedy]

Three for the price of one...

[geliedee]

Hi Richard,

it should always be number.

I used all the formula and it should actually passed but i got failed.

[geliedee]

here's the file.

[samba_ravi]

=IF(AND(I6="YSTP",E6<>"IT",F6=14,OR(G6={2,3,5,6,12}),H6<>4),"PASSED","FAIL")
Try this
In your 1st post you enter all the numbers as text string
H6<>4, in column H all numbers are 4 then how it will be Pass

[Glenn Kennedy]

Until we saw the sheet, we could not tell what was TEXT that looked like numbers and what were REALLY numbers...#
=IF(AND(I5="YSTP",E5<>"IT",F5=14,OR(G5={2,3,5,6,12}),H5<>4),"PASSED","FAIL")

[geliedee]

Is that based on this scenario below?


IF Account Group = YSTP
AND Sales Organization <> IT*
AND Distribution Channel = 14 only
AND Division = 02, 03, 05, 06, 12 only (no other Sales Area)
WHERE Condition Group 1 <> 04
THEN FAILED

[Glenn Kennedy]

I have just noticed that you have typed IT* and not IT. Was that intentional?

Otherwise all answers have been based on the scanario that you asked for.

[Richard Buttrey]

Hi Richard,

it should always be number.

I used all the formula and it should actually passed but i got failed.

It is incumbent on you to be quite clear when you post details without also providing the workbook. Excel is very fussy about stuff like numbers and text that looks like numbers, i.e. 2 and '02'. They may even LOOK the same when formatted but underlying they are not equivalent. I did mention this in my first post.

Here's the updated formula

Please Login or Register  to view this content.