To Pivot or not to Pivot

[Ucubia]

Hi

I have a worksheet containing nearly 1200 offices in Column A

Column B,C and D are just administration (office address, contact, tel)
Column E identified network bandwidth utilisation
Column F identifies the minimum bandwidth usage
Column G identifies the maximum bandwidth usage
Column H identifies the number of network resets

I want to be able to get the Top 10 for each of Column F,G and H and then display the corresponding site name from Column A

I am new to Pivot Tables, so if someone could give me some idea as to the best way to tackle this I would be grateful. Unfortunately, I am having to use the customer supplied Excel 2003 although I will best looking at offering Excel 2010 as a solution.

If There is a better way to do this other than Pivot Table then I would be interested to hear that as well.

Many thanks

 

[llenza]

If this is all you need to do. I recommend keeping it simple. In Excel 2000-2010 you can set a filter for "Top 10 Autofilter" to bottom (MIN) and top (MAX)

STEPS:
1. Sheet created
2. Enable AutoFilter
3. Under column "Min Bandwidth" click on arrow and select (TOP 10...)
4. "TOP 10 Autofilter" window displays, so change values to "Bottom, 10 and Items"
5. Click OK
6. Top 10 min item are displayed
7. To calculate MAX remove TOP 10... filter in MIN
8. Do the same that was done for MIN and do it for MAX (4. Values this time are "TOP, 10 and Items")

see illustration below:

Office	Network	Min Bandwith	Max Bandwidth
NY	750	100	1200
GA	800	150	1150
MD	850	200	1100
SC	900	250	1050
NC	950	300	1000
DE	1000	350	950
PA	1050	400	900
CA	1100	450	850
MN	1150	500	800
TX	1200	550	750
OH	1250	600	700
AL	1300	650	650

<tbody>
</tbody>

All inclusive and all values displayed

Office	Network	Min Bandwith	Max Bandwidth
NY	750	100	1200
GA	800	150	1150
MD	850	200	1100
SC	900	250	1050
NC	950	300	1000
DE	1000	350	950
PA	1050	400	900
CA	1100	450	850
MN	1150	500	800
TX	1200	550	750

<tbody>
</tbody>

Above autofilter set to top 10 MIN

Office	Network	Min Bandwith	Max Bandwidth
NY	750	100	1200
GA	800	150	1150
MD	850	200	1100
SC	900	250	1050
NC	950	300	1000
DE	1000	350	950
PA	1050	400	900
CA	1100	450	850
MN	1150	500	800
TX	1200	550	750

<tbody>
</tbody>

Above autofilter set to top 10 MAX

Good Luck!

 

[Ucubia]

Hi and thank you for the reply

This I was aware of the problem being that I need to dashboard thing with several small charts to show max bw sites, min bw sites, etc etc

Any thoughts?

Many thanks

 

[llenza]

If a Pivot Table is what you need create two pivot tables and then create charts from the two once that represents MIN and the other MAX.
To convert the main data into a pivot table convert it to a Link Table, see help for details or visit the youtube video feed Excel Magic #900 via the browse engine.

Good Luck!