Running total

[Dulanic]

Hello,

I am working on a excel sheet that tracks thousands of records. Basically, have 400+ employee's who have scores that need to be tracked. The scores are % based... to start we have 15+ columns but all that matter for these calculations is probably 3. We have score 1 which their required to hit 90% score 2 which is 80%, they are seperate scores. Some people dont count so we disqualify the formula from counting a 0. My current forumla is...

COUNTIF(K418,"<90")+COUNTIF(N418,"<80")-COUNTIF(N418,"=0") where the rows change for each record. We are counting the # of times they don't hit their goal. They can only be held accountable for one score a month not for both scores so created a 2nd column which has =IF(O418>=1,1,0) so if it came back with 2 counts it changed it to 1. OK, so background explained

Now the tricky part... we have a running total per month of how many times they did not hit their goal. We have a column with the mm/yyyy. So we have a pivot table which breaks down the associates by name department etc... and once completely drilled down into it gives a breakdown of their score for every month and then if they did or did not hit their goal by referencing the previous field I discussed. Currently it has a running sum, but I am stuck on the twist I need to throw in... if they meet their goal for 3 months straight, 1... we will call it "occurence" drops off. So for example.... Jan 1 Occurence, Feb 0, Mar 0, Apr 0. So in this case, Jan, Feb, Mar need a running total of 1. Once april hits, the running total needs to go back to 0. So if they got an "occurence" in May, the running total would go back to 1 instead of the current 2 my sum shows.

I imagine there has to be some way I can do this, but I have been thinking about this and hit a brick wall. Any idea's at all? For reference column A is the mm/yyyy (which can be reformatted differently pretty easily). Column K is score 1, column N is score 2, those are the 3 fields I reference for my forumulas currently, the pivot table uses everything else.

Edit:

Here is an example of one associate.... the top row is the average for the associate and the sum of "occurences"... I want to or plan to have a running total section added on the side however if possible. This associates running total should be 2 instead of 3 as there was a 3 month gap for Mar, Apr, May so it would have been -1 in May as explained above.

Doe, Jane103.77 88.82 3
1/1/2009 98.58 94.12 0
2/1/2009 101.62 70.59 1
3/1/2009 110.61 105.88 0
4/1/2009 131.78 117.65 0
5/1/2009 102.05 88.24 0
6/1/2009 110.46 58.82 1
7/1/2009 98.23 105.88 0
8/1/2009 86.82 70.59 1
9/1/2009 100.39 82.35 0
10/1/200 97.16 94.12 0

[DonkeyOte]

It would help I think if we know how (if) the data was sorted on PT source sheet ...

In essence I would say first you should be looking to calculate the running total on the source sheet adjacent to the transactions.

Regards the calculation...

If using XL2007 as implied then where you had a current occurrence you could use SUMIFS to establish the count of occurrences in the prior three months, if 0 then add nothing (to the current "1") else add the last running total figure found for that individual.

Use this new field in your PT - set to SUM.

On an aside, change: =IF(O418>=1,1,0) to =MIN(1,O418) ... more efficient.

[Dulanic]

It would help I think if we know how (if) the data was sorted on PT source sheet ...

In essence I would say first you should be looking to calculate the running total on the source sheet adjacent to the transactions.

Regards the calculation...

If using XL2007 as implied then where you had a current occurrence you could use SUMIFS to establish the count of occurrences in the prior three months, if 0 then add nothing (to the current "1") else add the last running total figure found for that individual.

Use this new field in your PT - set to SUM.

On an aside, change: =IF(O418>=1,1,0) to =MIN(1,O418) ... more efficient.

The data isn't really sorted for the most part within the source data, it can be sorted however... I have attached a small sample of source data (names replaced).

[DonkeyOte]

I will look at this in more depth - prob. tomorrow if not resolved by others in the meantime though I do have a couple of questions / points

Q: Is the code in G unique to the Associate ?

ie if 2 Joe Smith's exist which is the field to be used so as to be able to differentiate between the two individuals ?

Q: further to the above, when establishing occurrences is it based purely on the unique field or does one take into account dept, function etc etc... ?

Suggestion:

You can dispense with P altogether and simplify/streamline your formula in O with:

O15: =MIN(1,(K15<90)+AND(N15>0,N15<80))

[Dulanic]

I will look at this in more depth - prob. tomorrow if not resolved by others in the meantime though I do have a couple of questions / points

Q: Is the code in G unique to the Associate ?

ie if 2 Joe Smith's exist which is the field to be used so as to be able to differentiate between the two individuals ?

Q: further to the above, when establishing occurrences is it based purely on the unique field or does one take into account dept, function etc etc... ?

Suggestion:

You can dispense with P altogether and simplify/streamline your formula in O with:

O15: =MIN(1,(K15<90)+AND(N15>0,N15<80))

Thank's so much with your help so far. As for your question, yes there could be 2 Jane Smiths. Hasn't been an issue as we use the data by manager. G is a unique ID for each associate. I have left it using their names as easier to identify. And thanks for the help with the formula in O.... I couldnt get that narrowed down to a better formula, your's works great Realistically the best way would be to reference the unique ID to count occurences, Im just not that advanced. An associate can switch teams/departments, if it could follow between teams that would be WONDERFUL. The biggest challenge I think would be that the way it is drilled down is department, manager, associate for the pivot table. So when they swap teams, I guess if I did it by their ID, I would have to have a seperate area that can pull their running total of occurences or something along those lines.

[DonkeyOte]

See if the attached (2007 format) is along the right lines ?

I'm still not entirely convinced I follow the various requirements in terms of what constitutes a team swap etc etc but I would hope that the attached would give you some pointers nonetheless.

Given you don't have a record per month for each month you're obliged to conduct a few more SUMIFS functions than would ideally be the case...

[Dulanic]

See if the attached (2007 format) is along the right lines ?

I'm still not entirely convinced I follow the various requirements in terms of what constitutes a team swap etc etc but I would hope that the attached would give you some pointers nonetheless.

Given you don't have a record per month for each month you're obliged to conduct a few more SUMIFS functions than would ideally be the case...

That works great, I REALLY appreciate it. Just worked it into my pivot table now. I apologize for not being specific, by team swap I just meant that an associate can move from one manager to another or from one department to another. Also, managers can change etc... calculating based on their ID was perfect.

[Dulanic]

After running it on all 4000 records, it seems to be doing one thing that's not working.


Here is a example from one associate (some of the columns), it correctly does the running tally, but instead of taking away 1 single occurence after 3 months it took away all 3. How this needs to come out is 2, 3, 3, 3, 2, 1, 0. I've been trying to find a way to make it just -1 instead of reset but I am failing
69.22 10 5.5 64.71 1 2
64.91 10 10 117.65 1 3
127.82 10 10 117.65 0 3
125.53 10 7 82.35 0 3
96.01 10 10 117.65 0 3
101.52 10 10 117.65 0 0
106.81 8 8 117.65 0 0
113.37 8 6.4 94.12 0 0
116.79 8 6.8 100.00 0 0

[DonkeyOte]

It would be easier to analyse in a file...

[Dulanic]

My apologies, I copied it over to an excel sheet.

[DonkeyOte]

I'm rushing around a bit at present but in the interim you could try

Please Login or Register  to view this content.

I will endeavour to look at this in more depth later.

[Dulanic]

I want to offically say, you rock. It's close, not quite there. It subtracts one now, but if they have a 0 2 months in a row it still only subtracts 1 the first month with a 0, but not the 2nd. I attached again.

[DonkeyOte]

The formula is doing as expected - the issue is the ordering of your data.

In my initial upload I set the SUMIFS to be based upon: Associate, Group & Date
And on that basis the data B:N was sorted accordingly: G, C, B (in that order)

Given in the latest version you're only differentiating on basis of Name and Date you should alter the Sort order such that you are sorting only on basis of G & B (C should be excluded)

It is the ordering of your data in your recent file that results in P10 returning 3, given:

Please Login or Register  to view this content.

will return FALSE as there are in fact 4 occurrences that meet the above criteria (eg Associate=NBK1234, Date >= Jan 1 2009)

[Dulanic]

Ahhhh that would do it, very sorry. I had removed the department as a criteria as the fact that I want to track between functions if they move. I forgot to resort when that happened!

[DonkeyOte]

I had removed the department as a criteria as the fact that I want to track between functions if they move. I forgot to resort when that happened!

If you want to differentiate based on Associate, Function & Date you must do the following:

a) sort B:N by G, E & B respectively

b) modify running total formula such that

Please Login or Register  to view this content.

[Dulanic]

Hello again! Unfortunately, I am back for this same situation as there is a change that I just can not figure out how to work in. I have tried adding some IF, MIN, or MAX functions to try to work this in, but I can not get it to work correctly. I've tried adding another argument to the current MAX function, but everything I try is not quite working out OK.

Basically the change is that it can not remove 1 "occurence" every month, but 1 every 3 months. I have attached a sheet showing the correct calculations. I am sure I am close with the things I have tried, but I am missing something.... but I can not figure it out.

[Dulanic]

I believe I may have it working by adding a IF function into the MAX function to make sure it is not changing it more than once every 3 months for going down. I still need to do some more testing to make sure it is 100% working, but it is looking good. Don't think I wrote it in the most efficent way possible, buy eh as long as it works.

Before:
=SUM($O3,IF($G3<>$G2,0,MAX(0,SUM($P2)-(SUMIFS($O$1:$O2,$G$1:$G2,$G3,$B$1:$B2,">="&EDATE($B3,-3))=0))))
After:
=SUM($O4,IF($G4<>$G3,0,MAX(0,SUM($P3)-(SUMIFS($O$1:$O3,$G$1:$G3,$G4,$B$1:$B3,">="&EDATE($B4,-3))=0),IF(($P3+$P2+$P1)/3>$P3,$P3,0))))

I don't get why it works lol because the IF I added doesnt verify the ID from field G like the sumif does.... but I have tried every combination of data and it always comes out right so far...