Formula Needed

G

Guest

Guest
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top