|
Idiot
I Am Moron!


Registered: 11/27/05
Posts: 6,554
Loc: 41.90231, 12.45390
Last seen: 8 days, 7 hours
|
Help with Excel
#19098911 - 11/07/13 03:20 AM (10 years, 2 months ago) |
|
|
So, I work at a call center and if you know anything about call centers you'll know stats are a big deal. Anyways, my team is having a contest amongst ourselves and I'm building an excel spreadsheet, mostly out of my own interest in learning excel. We have several stats with several grades for several ranges with in the stats and after working on the spreadsheet for an hour or so I'm thinking excel can't handle the level of detail/automation that I want to throw at it. So, I'll post a pic and explain what I want to happen and those in the know can tell me how over my head I am.

First off are the stats, these include FCR, Compliance, RPP, Quality, and VOC. Second are the grades and ranges to get those grades, you can see the ranges to the right hand side and their associated stats. I understand the ranges aren't clear enough for calculations but hopefully they're understandable to people. Lastly, the competitors are in groups of two and their week totals are averaged to make the team total.
So, I want to be able to fill out the table and have the competitors "Week Total" automatically fill in with the cumulative scores reflected from the stats and "Team Total" to update as well. We have five weeks of this so it would be nice if the incomplete weeks don't affect the "Team Total" until the "Week Total" is filled in for that week.
Hopefully I've explained that clearly enough but either way I'm pretty confident that I'm using excel in a manner which it wasn't designed for and actually coding an application would better suit this task.
-------------------- Customize your Shroomery experience! Do not argue with an idiot. He will drag you down to his level and beat you with experience.
|
encryptor


Registered: 05/15/03
Posts: 1,154
Last seen: 18 days, 17 hours
|
Re: Help with Excel [Re: Idiot]
#19098999 - 11/07/13 04:36 AM (10 years, 2 months ago) |
|
|
it sounds like an optimization problem. Try to use the Solver tool. I'm pretty sure Excel can handle these simple numbers.
|
Brown Buffalo
paisley superstar



Registered: 09/14/13
Posts: 821
Last seen: 3 years, 9 months
|
Re: Help with Excel [Re: encryptor]
#19099634 - 11/07/13 10:05 AM (10 years, 2 months ago) |
|
|
If I understood well:
put that week 1 average value is in cell A1 week 1 average value is in cell A2 week 1 average value is in cell A3 week 1 average value is in cell A4 week 1 average value is in cell A5
Quote:
=IF(A1 <> ""; IF(A2 <> ""; IF(A1 <> ""; IF(A4 <> ""; IF(A5 <> ""; A1+A2+A3+A4+A5/5; A1+A2+A3+A4/4); A1+A2+A3/3);A1+A2/2);A1)
There may be some errors in the script since I dont remember very well Excel syntax and I dont have it right now to test. Instead of "A1+A2+A3+A4+A5/5" you can use the average function I think. You should put the code into team total cell and correct all the reference to the right cells. For what concern the cumulative week total:
=cell1+cell2+cell3... correct here the references to cells too
-------------------- "..Here I am, inside the bunker, behind mental steel walls 90 psycho-centimeters thick.."
|
elax420
Anal Destroyer


Registered: 10/16/12
Posts: 15,536
|
|
I’m not entirely sure what you are wanting to do. The numbers to the right are constants that determine the grade? or the averages of team 1?
If you want it to give a grade, just use an if() function using the parameters stated.
Excel can do much gnarlier shit than that man, don’t underestimate it. If() is one of the easiest functions in excel.....
Also you could make that spreadsheet a million times cooler and more impressive if you use the index Index and throw in a pivot table with a graph.
Im not going to do you work for you but using Pivot tables is really easy and self explanatory, and just google Index function and it will show you how to use it.
Edited by elax420 (11/07/13 12:49 PM)
|
Brown Buffalo
paisley superstar



Registered: 09/14/13
Posts: 821
Last seen: 3 years, 9 months
|
Re: Help with Excel [Re: elax420]
#19104394 - 11/08/13 08:44 AM (10 years, 2 months ago) |
|
|
^ yeah also because you can integrate visual basic directly so you can do almost everything youd need out of it
-------------------- "..Here I am, inside the bunker, behind mental steel walls 90 psycho-centimeters thick.."
|
|