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

Thread: Formula Needed

  1. #1
    Guest

    Default

    I have a spreadsheet with a list of some 5000 products with each product having a ranking between 1 and 5000 under 3 criteria as follows:

    A B C D
    Product GP Rank Units Rank COGS Ranks
    12345 221 5 281
    23456 3 1234 17
    45678 4231 2567 3214

    I want a formula for column E which says if the ranks in columns B,C & D are within range 1-300 then this is a gold product, if the ranks for any 2 of the columns is within range 1-300 then silver, if the rank for only 1 column is within 1-300 then bronze otherwise grey. The result of the proposed formula in column E in the above example would be:
    12345 Gold
    23456 Silver
    45678 Grey

    Hope this makes sense and hope someone can help.

    Thanks
    Bob

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-19 15:09, Anonymous wrote:
    I have a spreadsheet with a list of some 5000 products with each product having a ranking between 1 and 5000 under 3 criteria as follows:

    A B C D
    Product GP Rank Units Rank COGS Ranks
    12345 221 5 281
    23456 3 1234 17
    45678 4231 2567 3214

    I want a formula for column E which says if the ranks in columns B,C & D are within range 1-300 then this is a gold product, if the ranks for any 2 of the columns is within range 1-300 then silver, if the rank for only 1 column is within 1-300 then bronze otherwise grey. The result of the proposed formula in column E in the above example would be:
    12345 Gold
    23456 Silver
    45678 Grey

    Hope this makes sense and hope someone can help.

    Thanks
    Bob
    In E2 enter: =VLOOKUP(SUMPRODUCT((B2:D2>=1)*(B2:D2<=300)),{1,"Grey";2,"Silver";3,"Gold"},2,0)

    The results for your sample will be;

    Gold
    Silver
    #N/A [ not grey as you stated ]

    If so desired, expand the

    {1,"Grey";2,"Silver";3,"Gold"}

    for 0, something like:

    {0,"None";1,"Grey";2,"Silver";3,"Gold"}

    or just accept #N/A as result when your rules do not apply to B, C, & D values.

    Aladin

    PS. Bob-- Did you consider registering? We have now to many Anons around.

Some videos you may like

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
  •