+ Reply to Thread
Results 1 to 8 of 8

Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and...

  1. #1
    Registered User
    Join Date
    08-18-2017
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    40

    Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and...

    Creating new thread from the old one to comply with protonLeah's request:

    ---

    Hi,

    Trying to align this new data I came to a different question:

    In column F I want to output either the word "Premium", or "Not Premium" with the following rules:

    Output "Pure Premium" in column F when:
    -SSD "Y" and USD/Units =>700 (the combination of these two together)
    -Brand Cluster is "Apple"

    In the same formula, output "Not Premium" when:
    Column D (Gaming Y/N) is "Gaming"

    Sorry to ask all these question, just that logic/math is not my forte, thank you for your help.

    Attaching a new workbook as an example with more clear information.

    Also as a reference I was using this formula before: =IF(AND([@[Gaming Y/N]]="Gaming",OR([@USD]/[@Units]>=700,[@[Brand Cluster]]="Apple",[@[SSD Y/N]]="Y")),"Not Premium",IF(OR([@USD]/[@Units]>=700,[@[Brand Cluster]]="Apple",[@[SSD Y/N]]="Y"),"Premium","Not Premium")) <--- however in this logic, I'm not combining the restriction of SSD "Y" and USD/Units all together as one restriction where I want to have all this as one rule.


    D2S
    Attached Files Attached Files
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and..

    This seems to work:

    =IF(Table2[@[Gaming Y/N]]="Gaming","Not ", IF(OR(Table2[@[SSD Y/N]]="N",AND(Table2[@[SSD Y/N]]="Y",Table2[@Column1]<700)), "Not ", "")) &"Premium"
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    08-18-2017
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    40

    Re: Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and..

    Hi JBeaucaire,

    It works in the example I attached, however, I need to include also the restriction of the word "Apple", because when I apply it in my Database it will not bring me the desired result, what I want to say in the formula is:

    If column "Gaming Y/N" (Col E) has the text "Gaming", bring "Not Premium", but if the USD price per unit is Above $700 (Col C) and has an SSD "Y" (Col F) (both of these being one rule), and my Brand is Apple (Col D), bring me "Premium", if not bring "Not Premium".

    Let me know if the above explanation works.

    Thanks,
    D2s

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and..

    .
    Perhaps this in G2 and drag down :

    Please Login or Register  to view this content.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and..

    So this then?:

    =IF(Table2[@[Gaming Y/N]]="Gaming","Not ", IF(OR(Table2[@[SSD Y/N]]="N",AND(Table2[@[SSD Y/N]]="Y",Table2[@Column1]<700,Table2[@[Brand Cluster]]<>"Apple")), "Not ", "")) &"Premium"

  6. #6
    Registered User
    Join Date
    08-18-2017
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    40

    Re: Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and..

    Hi Logit,

    Thanks for your reply, I tried the formula but I will only get a result for lines 4 to 6, the rest of the lines there is a blank.

    I want to apply the following rules to bring "Premium"
    -1st rule: If SSD = "Y" and USD is above $700 (this in one segment)
    -2nd rule: If Brand Cluster = "Apple"

    Output "Non Premium"
    -3rd rule: If Gaming Y/N = "Y"


    So this would mean the following:
    All Apple units are Premium, but is one is classified as Gaming Y/N = "Y", will return "Non Premium"
    If my combination of above 700 & SSD is "Y", classify at "Premium" (this would mean that all brands that fall into this category will be "Premium" but also if Gaming is "Y", I will output "Non Premium")

  7. #7
    Registered User
    Join Date
    08-18-2017
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    40

    Re: Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and..

    Hi Beau,

    Still got the issue, I'll be looking on my side meanwhile as well.

    D2S

  8. #8
    Registered User
    Join Date
    08-18-2017
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    40

    Re: Bring "Premium" when Price is Above $700 and SSD "Y", also when Brand is "Apple" and..

    I got it the answer is:

    =IF([@[Gaming Y/N]]="Gaming","Not Premium",IF(OR(AND([@[SSD Y/N]]="Y",[@[USD / Unit]]>=700),[@[Brand Cluster]]="Apple"),"Premium", "Not Premium"))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. [SOLVED] If Not ActiveSheet.Range("A1").Value Like "apple" Then MsgBox "Error"
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2014, 02:16 PM
  5. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  6. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1