Excel Help Needed

.Kencs · 5 Jan 2011 at 08:51

I need to know if this is possible in Excel:

For my job, I am monitoring which machines are repaired the most to see if there is a common trend.

Example:

PC 1
PC 2
PC 3
PC 4
PC 5
All the way to 300

Everytime 1 is repaired, I put a number next to it.
Is there a way to create a list of the top 5 only so everytime I add a number to 1 of the PCs, the list updates, like when you auto-add numbers.

Hope this makes sense.

Jed Shields · 5 Jan 2011 at 09:38

Starter for 10:

Add another column called Rank and use the Rank function. This gives a numerical ranking of the repaired quantities. If there are two or more that are identical then they both get the same rank. Example:

If A1 to A300 is used for your quantities:

B1=RANK(A1,$A$1:$A$300,1)
B2=RANK(A2,$A$1:$A$300,1)
....
B300=RANK(A300,$A$1:$A$300,1)

The 1 at the end can be changed to a 0 and this will affect the ranking from top to bottom:

Repair RANK = 1 RANK = 0
20 4 1
15 2 3
17 3 2
12 1 4

Jed Shields · 5 Jan 2011 at 09:40

Aha, the final numbers have lost their spacing.

First column = Qty
Second Column = Rank using 1 as the order option
Third column = Rank using 0 as the order option

.Kencs · 5 Jan 2011 at 09:40

Jed Shields said:
Starter for 10:

Add another column called Rank and use the Rank function. This gives a numerical ranking of the repaired quantities. If there are two or more that are identical then they both get the same rank. Example:

If A1 to A300 is used for your quantities:

B1=RANK(A1,$A$1:$A$300,1)
B2=RANK(A2,$A$1:$A$300,1)
....
B300=RANK(A300,$A$1:$A$300,1)

The 1 at the end can be changed to a 0 and this will affect the ranking from top to bottom:

Repair RANK = 1 RANK = 0
20 4 1
15 2 3
17 3 2
12 1 4

I regret asking, seems complicated lol...

Jed Shields · 5 Jan 2011 at 10:02

I've got it in a spreadsheet if you want me to email it over so you can see it working ... PM me if you'd like it

.Kencs · 5 Jan 2011 at 10:05

Jed Shields said:
I've got it in a spreadsheet if you want me to email it over so you can see it working ... PM me if you'd like it

I dont have access to PM you :confused:

Jed Shields · 5 Jan 2011 at 10:14

Just added you. Alternatively, go to the Contact page on my website and email me using that. Address is in my sig

.Kencs · 5 Jan 2011 at 10:43

Click my profile and use my email address in my Biography

Jed Shields · 5 Jan 2011 at 11:15

Email sent, macro spreadsheet attached

MarcLister · 5 Jan 2011 at 12:17

Jed Shields said:
PM me if you'd like it

PMs are disabled for users on Overclockers.

Jed Shields · 5 Jan 2011 at 16:58

MarcLister said:
PMs are disabled for users on Overclockers.

Any particular reason?

MarcLister · 5 Jan 2011 at 20:21

Jed Shields said:
Any particular reason?

Er I think its to reduce the load on the server hosting the website.

http://forums.overclockers.co.uk/faq.php?faq=vb_board_usage#faq_vb_pm_explain

Jed Shields · 6 Jan 2011 at 11:32

MarcLister said:
Er I think its to reduce the load on the server hosting the website.

http://forums.overclockers.co.uk/faq.php?faq=vb_board_usage#faq_vb_pm_explain

Fair enough