Advanced IF statement

[gipdip]

Hi there,

My excel skills are pretty average so im not sure if its possible to do but i want to try doing the below.

Type (A1)	Market Cap (B1)	Daily trade Value (C1)	Yes/No (D1)	Reason (E1)
Drop down box with 3 options 'IPO', 'Listed', 'Code change'			Formula	Formula

This is the table I want to create I need help with the formulas for the last two columns.

I want to be able to create a formula that uses the words from the Type column to use and spit out the correct information.

Yes/No Column (D1)
If A1="IPO" and B1>$5,000,000 then "Yes" if not "No" or if 'A1="Code Change" and C1>$100,000 then "Yes" if not "No" or if A1="Listed" and C1>$100,000 then "Yes" if not "No"

*Depending on the text in A1 will depend on which columns are used

Reason (E1)
If A1="IPO" and B1>$5,000,000 then "Greater than $5M" if not "Less than $5M"or if 'A1="Code Change" and C1>$100,000 then "Daily trade Value greater than $100k" if not "Daily trade Value less than $100k" or if 'A1="Listed" and C1>$100,000 then "Daily trade Value greater than $100k" if not "Daily trade Value less than $100k"

*Depending on the text in A1 will depend on which columns are used

Any help would be great

 

[sxhall]

Think I have translated them both correctly...

Yes/No Column (D1)

=IF(AND(A2="IPO",B2>5000000),"Yes",IF(AND(A2="Code Change",C2>100000),"Yes",IF(AND(A2="Listed",C2>100000),"Yes","No")))

Reason Column (E1)

=IF(AND(A2="IPO",B2>5000000),"Greater than $5M",IF(AND(B2<5000000,OR(A2="Code Change",C2>100000 )),"Daily trade Value greater than $100k",IF(AND(C2>100000,OR(C2<100000,A2="Listed")),"Daily trade Value greater than $100k","Daily trade Value less than $100k")))

Steven

 

[DanteAmor]

Try this:

varios 01jun2021.xlsm
	A	B	C	D	E
1	Type	Market Cap	Daily	Yes/No	Reason
2	IPO	6,000,000		Yes	Greater than $5M
3	Code change		200,000	Yes	Daily trade Value greater than $100k
4	Listed		300,000	Yes	Daily trade Value greater than $100k
5	IPO	4,000,000		No	Less than $5M
6	Code change		80,000	No	Daily trade Value less than $100k
7	Listed		50,000	No	Daily trade Value less than $100k
Hoja11 (2)

Cell Formulas
Range	Formula
D2:D7	D2	=IF(OR(AND(A2="IPO", B2>5000000), AND(OR(A2={"Code Change","Listed"}), C2>100000)),"Yes", "No")
E2:E7	E2	=IF(A2="IPO",IF(B2>5000000, "Greater than $5M", "Less than $5M"), IF(OR(A2={"Code Change","Listed"}),IF(C2>100000,"Daily trade Value greater than $100k", "Daily trade Value less than $100k")))

 

[gipdip]

Try this:

varios 01jun2021.xlsm
	A	B	C	D	E
1	Type	Market Cap	Daily	Yes/No	Reason
2	IPO	6,000,000		Yes	Greater than $5M
3	Code change		200,000	Yes	Daily trade Value greater than $100k
4	Listed		300,000	Yes	Daily trade Value greater than $100k
5	IPO	4,000,000		No	Less than $5M
6	Code change		80,000	No	Daily trade Value less than $100k
7	Listed		50,000	No	Daily trade Value less than $100k
Hoja11 (2)

Cell Formulas
Range	Formula
D2:D7	D2	=IF(OR(AND(A2="IPO", B2>5000000), AND(OR(A2={"Code Change","Listed"}), C2>100000)),"Yes", "No")
E2:E7	E2	=IF(A2="IPO",IF(B2>5000000, "Greater than $5M", "Less than $5M"), IF(OR(A2={"Code Change","Listed"}),IF(C2>100000,"Daily trade Value greater than $100k", "Daily trade Value less than $100k")))

This worked!! Thank you so much thats awesome!

 

[Peter_SSs]

Welcome to the MrExcel board!

If you are interested, here are a couple of shorter options that I think also do what you want.

21 06 02.xlsm
	A	B	C	D	E
1	Type	Market Cap	Daily	Yes/No	Reason
2	IPO	6000000		Yes	Greater than $5M
3	Code change		200000	Yes	Daily trade Value greater than $100k
4	Listed		300000	Yes	Daily trade Value greater than $100k
5	IPO	4000000		No	Less than $5M
6	Code change		80000	No	Daily trade Value less than $100k
7	Listed		50000	No	Daily trade Value less than $100k
Sheet2 (2)

Cell Formulas
Range	Formula
D2:D7	D2	=IF(IF(A2="IPO",B2/50,C2)>100000,"Yes","No")
E2:E7	E2	=IF(A2="IPO",IF(D2="No","Less","Greater")&" than $5M","Daily trade Value "&IF(D2="No","less","greater")&" than $100k")

 

[DanteAmor]

This worked!! Thank you so much thats awesome!

I'm glad to help you. Thanks for the feedback.