Formula Needed

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

[Aladin Akyurek]

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.