Help in using If AND OR conditions in the formula

[anees_a]

Hi,

I am having trouble with formulas below, when i try to use AND, OR, i having trouble . i need the below formulas to be one formula kindly help.


=IF(B1=SG,IF(A1="I",0,IF(A1="A",0,IF(A1="P",0,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=LQT,IF(A1="I",150,IF(A1="A",150,IF(A1="P",150,IF(A1="D",44,IF(A1="W",44,IF(A1="O",44,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=MLT,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=IBW,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",10+(C2*15%),IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))
=IF(B1=JL,IF(A1="I",151,IF(A1="A",151,IF(A1="P",151,IF(A1="D",0,IF(A1="W",0,IF(A1="O",0,IF(A1="B",0,IF(A1="AQ",0,IF(A1="C",0,))))))))))

Thanks in Advance.

[haripopuri]

What are you trying to do with this long formula. Can't you simply using a VLOOKUP or something?

[Special-K]

1) In that first statement the result is 0 if B1=SG and A1 is one of A P D W O B AQ or C.
You have no final value if all the above fails so the result will ALSO be 0
Therefore the entire statement is equivalent to IF(B1=SG,0,0) which doesnt sound correct.

2) Shouldnt those B1 test values be in quotes or are they defined names?

[anees_a]

Hi,

@haripopuri
I have no idea in using VLOOKUP

@Special-K
1)Yes I need SG to be zero and if the B1 is SG the the display should be zero.
2)B1 values are names hence in quotes.

just to give u a understanding if B1 is SG then the values are zero, and if B1 is LQT/IBW/JL/xyz then it should check A1 and as per the values of the A1 the results should be the output.

The reason i am using zeros for SG is in case if i need to use some values in future.

basacily if A1 is I or A or P or D or W or O or B or AQ or C and B1 is SG then I = 0, A = 0, P=0, D=0, W=0, O=0, B=0, AQ=0, C=0 or B1 is LQT then I = 150, A = 150, P=150, D=44, W=44, O=44, B=0, AQ=0, C=0 or if B1 is MLTW then I = 151, A = 151, P=151, D=0, W=0, O=0, B=0, AQ=0, C=0 or if B1 is IBW then I = 0, A = 0, P=0, D=10+(d3*15%), W=0, O=0, B=0, AQ=0, C=0 or if B1 is JL then I = 151, A = 151, P=151, D=0, W=0, O=0, B=0, AQ=0, C=0

[Saarang84]

Hi,

Can u post a workbook with sample data ? (remove sensitive information)

[FDibbins]

Here is a different approach that would use INDEX/MATCH. I created a small table....

	B	C	D	E	F	G
15		SG	LQT	MLT	IBW	JL
16	I	0	150	151	151	151
17	A	0	150	151	151	151
18	P	0	150	151	151	151
19	D	0	44	0	10+(c2*15&)	0
20	W	0	44	0	0	0
21	O	0	44	0	0	0
22	B	0	0	0	0	0
23	AQ	0	0	0	0	0
24	C	0	0	0	0	0

=INDEX($C$16:$G$24,MATCH(B1,$B$16:$B$24,0),MATCH(A1,$C$15:$G$15,0))

for F19 (10+(c2*15%) ) instead of just C2, use =C2

[anees_a]

Dear Saarang84

I have attached a sample hope you understand the requirement

i am trying to use this formula i thig i am execeeding the if Conditions.

[Saarang84]

Hi,

Welcome to the Forum.

Can u explain briefly what you are trying / want to achieve through these formulas in column G and J in your attachment?

If you can give us information about the different types of codes used in Status and Sub-con columns, we can code the formula better instead of your Frankenstein IF formula.

[anees_a]

Hi Saarang,

I do agree that their is a better way than my Frankenstein 😁, anyways jokes apart, I will list what each column contains and what is that I want.

First I use this to manage my projects, now

Columns

- C is Qty
- D I need to take 15% of the value in calculation if Columns E Has D
- F is where for project status like (C for complete, IP for in progress, H for Hold)
- G is my price what I get ( as per values in columns E
- H is my invoice values (i.e. G*C, if E is D then G*C+(d*15%))
- I is my sub contractor diffrent companies do diffrent services and my rates are different wit each of them as per their services, i keep adding ne sub cos with different pricing structure.
- J is their price
-K is J*C and if required d*x% i.e. J*C+(D*15)


The reason I need zero in column J if Column I is SG as I am myself doing the service so no price in J


Their are even more columns in the actual sheet before c and subcon.
Now if you see the formula listed.

Now in the sheet as u can see in J3 the price structure & in Column G

If their is a better way to do do let me know


Sent from my RM-821_im_mea3_306 using Tapatalk