Pretty simple IF equation help

ryan640

Board Regular
Joined
Jan 14, 2015
Messages
75
Hello Everyone,

I have two columns that I will be looking at. The first has a number (15.0101-15.1201) the second has names (google, Facebook twitter). I need to make an formula that can look at the second column and if it is google and less than 15.0501 write the word sunk. If the second column is google and greater than 15.0501 then I need it to say forecast. The tricky part comes when I need to to be able to change if column 2 is one of the other choices. For example if column 2 is Facebook then I need it to say sunk when its less than 15.0701. I have about 10 company names but figure if anyone could help me I could finish the formula.

ReleaseCompanyanswer I need
17.0201GoogleSunk
17.0601FacebookSunk
17.0501GoogleForecast

<tbody>
</tbody>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hello Everyone,

I have two columns that I will be looking at. The first has a number (15.0101-15.1201) the second has names (google, Facebook twitter). I need to make an formula that can look at the second column and if it is google and less than 15.0501 write the word sunk. If the second column is google and greater than 15.0501 then I need it to say forecast. The tricky part comes when I need to to be able to change if column 2 is one of the other choices. For example if column 2 is Facebook then I need it to say sunk when its less than 15.0701. I have about 10 company names but figure if anyone could help me I could finish the formula.

ReleaseCompanyanswer I need
17.0201GoogleSunk
17.0601FacebookSunk
17.0501GoogleForecast

<tbody>
</tbody>

In column 3....assuming this is ABC, so in column C....

=IF(AND(B2="google",A2<15.0501),"Sunk",IF(AND(B2="google",A2>15.0501),"Forecast",IF(AND(B2="Facebook",A2<15.0701),"Sunk",IF(AND(B2="Facebook",A2>15.0701),"Forecast"))))
 
Upvote 0
I'd suggest creating a table with the values in it. That would make the formula much shorter:

ABCDEFG
1ReleaseCompanyanswer I needCompanyPivot value
217.0201GoogleSunkSunkGoogle17.0501
317.0601FacebookSunkSunkFacebook17.0701
417.0501GoogleForecastForecast

<tbody>
</tbody>
Sheet13

Worksheet Formulas
CellFormula
C2=IF(A2< VLOOKUP(B2,$F$2:$G$3,2,0),"Sunk","Forecast")
D2=IF(A2< INDEX({17.0501,17.0701},MATCH(B2,{"Google","Facebook"},0)),"Sunk","Forecast")

<tbody>
</tbody>

<tbody>
</tbody>



If you want it all in one formula, an INDEX/MATCH would be a bit more succinct. But I'd still go with the table since it would be much easier to maintain.
 
Last edited:
Upvote 0
Eric I have to say you are a genius. The index match you provided is amazing. Thank you. I have one more request if you are up to the challenge. I need to add in a third dimension. Instead of just sunk and forecast I need it to indicate "2018 carry over" if the release is 18.0101. For example Google is "Forecast" if it is greater than 17.0501 but i need it to only be "Forecast" till 17.1201. Just like in the example that could vary depending on the company.
 
Upvote 0
Roderick, Thank you for your input as well. I originally was using your formula but I had difficulty scaling it to fit all the companies I track. If you have suggestions on building off Erics formula they would be greatly appreciated. Both of you are wizards. Thanks again.
 
Upvote 0
If I understand what you want, then this should work:

ABCDEFGHI
1ReleaseCompanyanswer I needCompanySunkForecast2018 Spend
217.0201GoogleSunkSunkGoogle017.050117.1201
317.1305Facebook2018 Spend2018 SpendFacebook017.070117.1301
417.0501GoogleForecastForecast
5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet14

Worksheet Formulas
CellFormula
C2=LOOKUP(A2,INDEX({0,17.0501,17.1201;0,17.0701,17.1301},MATCH(B2,{"Google","Facebook"},0)),{"Sunk","Forecast","2018 Spend"})
D2=LOOKUP(A2,INDEX($G$2:$I$5,MATCH(B2,$F$2:$F$5,0),0),$G$1:$I$1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



I'd still recommend the formula in D2 that references the table, since it's much easier to modify the table than the formula. You can put the table on another sheet if you like. Leave empty rows at the end to add new companies.

If you like the all-in-one formula, look at C2. There are 3 internal array constants. The last is the 3 options to print, the middle is the list of companies, and the first is the trigger values. The first 3 values go with Google, then there's a semicolon, then 3 values for Facebook. "Sunk" will be shown for 0-17.0501, "Forecast" for 17.0501-17.1201, and "2018 Spend" for 17.1201-up.

As far as books, I'm sure there are some good ones, but since I'm largely self-taught, I can't really recommend any. My technique has been to find a formula that looks interesting, here or on some other forum, deconstruct it until I understand it, then practice it. I can refer you to an incredible list that hiker95 has compiled of various web resources. This list is a little overwhelming, but just pick one interesting topic and go through it.

https://www.mrexcel.com/forum/excel...best-way-learn-visual-basic-applications.html

Some people really like the YouTube videos here:

https://www.mrexcel.com/forum/gener...t-using-excel-again-after-long-long-time.html


Good luck!
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top