Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Pretty simple IF equation help

  1. #1
    Board Regular
    Join Date
    Jan 2015
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Roderick_E's Avatar
    Join Date
    Oct 2007
    Posts
    1,495
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pretty simple IF equation help

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

    =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"))))
    for more go to rodericke.com/xlsuper

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,947
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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")
    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 06: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

  4. #4
    Board Regular
    Join Date
    Jan 2015
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Jan 2015
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    4,947
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    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:

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


    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

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.
     


DMCA.com