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 Sunk 17.0601 Sunk 17.0501 Forecast
for more go to rodericke.com/xlsuper
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 Sunk Sunk 17.0501 3 17.0601 Sunk Sunk 17.0701 4 17.0501 Forecast Forecast Sheet13
Worksheet Formulas
Cell Formula 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")
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 by Eric W; Apr 13th, 2017 at 07:40 PM.
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
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.
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.
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 Sunk Sunk 0 17.0501 17.1201 3 17.1305 2018 Spend 2018 Spend 0 17.0701 17.1301 4 17.0501 Forecast Forecast 5 Sheet14
Worksheet Formulas
Cell Formula 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)
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:
https://www.mrexcel.com/forum/genera...long-time.html
Good luck!
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
Like this thread? Share it with others