Thanks:  0
Likes:  0

Thread: Pretty simple IF equation help

1. Pretty simple IF equation help

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.

 Release Company answer I need 17.0201 Google Sunk 17.0601 Facebook Sunk 17.0501 Google Forecast

2. Re: Pretty simple IF equation help

Originally Posted by ryan640
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.

 Release Company answer I need 17.0201 Google Sunk 17.0601 Facebook Sunk 17.0501 Google Forecast
In column 3....assuming this is ABC, so in column C....

3. Re: Pretty simple IF equation help

I'd suggest creating a table with the values in it. That would make the formula much shorter:

A B C D E F G
1 Release Company answer I need Company Pivot value
2 17.0201 Google Sunk Sunk Google 17.0501
3 17.0601 Facebook Sunk Sunk Facebook 17.0701
4 17.0501 Google Forecast Forecast
Sheet13

Worksheet Formulas
Cell Formula
C2 =IF(A2< VLOOKUP(B2,\$F\$2:\$G\$3,2,0),"Sunk","Forecast")

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.

4. Re: Pretty simple IF equation help

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.

5. Re: Pretty simple IF equation help

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.

6. Re: Pretty simple IF equation help

If I understand what you want, then this should work:

A B C D E F G H I
1 Release Company answer I need Company Sunk Forecast 2018 Spend
2 17.0201 Google Sunk Sunk Google 0 17.0501 17.1201
3 17.1305 Facebook 2018 Spend 2018 Spend Facebook 0 17.0701 17.1301
4 17.0501 Google Forecast Forecast
5
Sheet14

Worksheet Formulas
Cell Formula
D2 =LOOKUP(A2,INDEX(\$G\$2:\$I\$5,MATCH(B2,\$F\$2:\$F\$5,0),0),\$G\$1:\$I\$1)

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-...lications.html

Some people really like the YouTube videos here:

Wanting to start using excel again after a long, long time!!

Good luck!

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.