Advanced IF statement

gipdip

New Member
Joined
Jun 1, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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'FormulaFormula

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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
150
Think I have translated them both correctly...

Yes/No Column (D1)
Excel Formula:
=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)
Excel Formula:
=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

Well-known Member
Joined
Dec 3, 2018
Messages
13,750
Office Version
  1. 2007
Platform
  1. Windows
Try this:

varios 01jun2021.xlsm
ABCDE
1TypeMarket CapDailyYes/NoReason
2IPO6,000,000YesGreater than $5M
3Code change200,000YesDaily trade Value greater than $100k
4Listed300,000YesDaily trade Value greater than $100k
5IPO4,000,000NoLess than $5M
6Code change80,000NoDaily trade Value less than $100k
7Listed50,000NoDaily trade Value less than $100k
Hoja11 (2)
Cell Formulas
RangeFormula
D2:D7D2=IF(OR(AND(A2="IPO", B2>5000000), AND(OR(A2={"Code Change","Listed"}), C2>100000)),"Yes", "No")
E2:E7E2=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

New Member
Joined
Jun 1, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Try this:

varios 01jun2021.xlsm
ABCDE
1TypeMarket CapDailyYes/NoReason
2IPO6,000,000YesGreater than $5M
3Code change200,000YesDaily trade Value greater than $100k
4Listed300,000YesDaily trade Value greater than $100k
5IPO4,000,000NoLess than $5M
6Code change80,000NoDaily trade Value less than $100k
7Listed50,000NoDaily trade Value less than $100k
Hoja11 (2)
Cell Formulas
RangeFormula
D2:D7D2=IF(OR(AND(A2="IPO", B2>5000000), AND(OR(A2={"Code Change","Listed"}), C2>100000)),"Yes", "No")
E2:E7E2=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,021
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1TypeMarket CapDailyYes/NoReason
2IPO6000000YesGreater than $5M
3Code change200000YesDaily trade Value greater than $100k
4Listed300000YesDaily trade Value greater than $100k
5IPO4000000NoLess than $5M
6Code change80000NoDaily trade Value less than $100k
7Listed50000NoDaily trade Value less than $100k
Sheet2 (2)
Cell Formulas
RangeFormula
D2:D7D2=IF(IF(A2="IPO",B2/50,C2)>100000,"Yes","No")
E2:E7E2=IF(A2="IPO",IF(D2="No","Less","Greater")&" than $5M","Daily trade Value "&IF(D2="No","less","greater")&" than $100k")
 

Forum statistics

Threads
1,143,677
Messages
5,720,259
Members
422,273
Latest member
linds75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top