• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel spreadsheet required for work, is this possible?

H

hunimonster22

New Member
Hi, I was wondering if anyone could help me with the following scenario and let me know if it can be done in excel.


My scenario is this:

11 different shops

2-3 staff per shop (and not always the same staff at each shop)

Currently recording the number of orders each staff member takes and recording out of those orders how many promotional items were sold (this is manually entered from shops daily reports) from that I am working out the percentage of promotional items they sell on their total orders (simple calculation: promo items/total orders*100)


This would be entered daily and from this I want it to calculate weekly totals and monthly totals to be able to work out who is selling the most promotional items at which shop. The ones that sell on 50% or over of their sales, 40% or over, 30% or over, etc. Therefore would also need a table that ranks the staff i.e. a table of who is 1st 2nd 3rd etc...I know how to use the rank function but do not know how to rank the names in order.


I would be very grateful if someone could let me know 1) if this is possible (or if a database would be easier) and 2) how to go about it as only have basic knowledge of excel and simple formulae.


Look forward to hearing from you.

Many thanks

H
 
Welcome to the forums!

The key is how you setup your data table. You should have some columns like this:

[pre]
Code: 
Date	    Shop	Staff	# of Orders	# of Promos
1-Jan-12	A	Joe	5		2
2-Jan-12	A	Tim	8		4
3-Jan-12	B	Sue	6		1
4-Jan-12	B	John	5		4
5-Jan-12	A	Tim	16		5
6-Jan-12	A	Joe	19		4
[/pre]
This layout would make it very easy to construct a PivotTable, which in turn would let you quickly see the top x% of people who are meeting a certain goal. PivotTables let you group rows, which would let you see stats by week/month/year.

Adding a calculated field to PT:

http://chandoo.org/wp/2010/02/25/p-l-reports-calculated-fields-3/


Show top 10:

http://blog.contextures.com/archives/2012/06/05/compare-top-and-bottom-sales-in-pivot-table/


Feel free to search this site or Debra's site for help on using PivotTables. Again, the biggest help you can give yourself is in the data setup.
 
Thank you Luke M for the prompt reply! And thanks for the links which I will look at in more detail.


That is how I have currently started the spreadsheet but I am finding it difficult to work out how to rank the staff each week based on their figures. Or do I have to manually filter the table for a certain member of staff between certain dates and work it out like that?
 
Using the contextures site as a reference, once you have the info in PivotTable setup, you should be able to sort the Staff by the %. Then, the rank is visually seen by the order in which they are displayed.
 
@SirJB7

Hi, myself!

Am I wrong or spreadsheets were intended for fun? Who'd guess about people using them for work?

:)

Regards!
 
Good day Lymm

"Spreadsheets make work fun." Will have to think on that one......but if you realy want fun at work and the chance to really really mess your head up try making a user friendly input data base in ACCESS
 
You must log in or register to reply here.
Back
Top