Ranking data (based on value selected in dropdown)

[D_N_L]

Hi all,

I have attached an example worksheet.

Essentially, I'm trying to rank (high - low) the scores of whatever business is selected in the drop-down box (A8). So if I were to select 'Business 1' the cells highlighted in green would become what i've written in them and so on for 'Business 2', etc.

Any ideas?

[dilipandey]

Hi Plumby,

This is interesting.
See the attached solution and let me know if this is what you were looking for. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[D_N_L]

Hi Dilipandey,

This is perfect!! Just one thing - if the numbers are the same value it returns #N/A. You can see this when I choose 'Business2'.

Any way around this? But this is brilliant so far thank you!

[dilipandey]

Hi Plumby,

This is happening because of Rank methodology we have chosen where rank 4 and 5 are not there which has been eaten by duplicate appearance of rank 3. So it is not formula issue but the property of rank formula. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[D_N_L]

Thanks Dilipandey. I see what you mean about the rank methodology. Is there anyway do you think to put an IF in there - so if there is a duplicate it resolves it...

Thanks for your time!

[dilipandey]

Ok..

See the attached file which is enhanced to handle this type of situation

Regards,
DILIPandey

<click on below 'star' if this helps>

[D_N_L]

Wow!!!

Thank you very much. Rep left. What a nice man!

[dilipandey]

You are welcome Plumby..

keep on coming up with these types of queries... as this refreshes me

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[D_N_L]

Will do!!

(I've currently got an unanswered post in the Programming sub-forum if you fancy it...)

I'm trying to learn Excel. This forum is brilliant place tho.

[dilipandey]

Thanks for encouraging words..
I will look out for that post in said forum...

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>