To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Pimp my ride (excel version)

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Pimp my ride (excel version)

Post by crispybits »

OK so I gave it a deliberately misleading title, but I think I need a little help with this because I've been staring at it for so long that it's become a monster.

I have a spreadhseet with four columns:

A - Project status
B - Project deadline
C - Project delivered date
D - Project re-delivered date

The project status can be one of 5 things:
"On schedule", "Behind schedule", "Customer query", "Management query", "Complete"

The project deadline is fairly self explanatory, it's the date by which that particular project must be delivered.

The project delivery date is the date the project has actually been delivered.

Sometimes a project gets delivered, and then the outcome is rejected so it gets thrown back into development. In these cases the status would change back to "On schedule" because the deadline gets reset, but the project delivery date needs to remain intact for finance purposes.

We base a report sheet off that, that lists the total amount of projects we have targetted to get delivered on any given day, and the actual number of projects delivered that day.

Day......Mon.....Tues.....Wed.....Thurs.....Fri
Target...X..........X..........X.........X...........X
Actual...X..........X...........X.........X...........X

Now the actual is really easy for excel.

COUNTIF(C=date)+COUNTIF(D=date)

I'm struggling massively with the target formula. I can easily write a formula that can look into days ahead and read those targets correctly (COUNTIFS(A="On schedule",B=date)) but those targets disappear when we actually complete projects, and I need the targets to remain intact even after a project is delivered.

One of the things making it much more challenging is the fact that if a project is completed during the week before it's deadline, it no longer counts for the target. So a project targetted for a Tuesday, but delivered the previous Friday, doesn't count in the target. We are only interested for the targets in projects not delivered yet or that have been delivered earlier during the same week. The 2 query statuses don't count for targets either, they just get ignored.

At the moment the closest thing I have to a target formula looks something like:

COUNTIF(B=date)-COUNTIFS(B=date,A="Customer query")-COUNTIFS(B=date,A="Management query")-COUNTIFS(B=date,C<date-@)+COUNTIFS(B=date,C<date-@,A="Management query")+COUNTIFS(B=date,C<date-@,A="Customer query")

Where @=depending on the day the formula is on a number to disqualify the previous week's delivered projects

Am I missing something really obvious or do I really need that ridiculous length of formula (in the real world version there's another couple of variables that have to be checked inside every single COUNTIF by the way - what you're seeing here is the easy to read distilled version)

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: Pimp my ride (excel version)

Post by Surprised »

The formula isn't that long. I've had formulas in Excel 2000 that went up to the 1024 character limit
An array formula might simplify things.

User avatar
Mystery
FISO Knight
Posts: 13816
Joined: 13 Oct 2005, 18:33
Location: Just about here
FS Record: 116th in TFF 05/06
Contact:

Re: Pimp my ride (excel version)

Post by Mystery »

Will come back to this later but already struggling with the double counting of delivered projects and redelivered projects. Do you really mean that?

User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Re: Pimp my ride (excel version)

Post by crispybits »

The formula I had didn't have the last bits, it looked like:

COUNTIF(B=date)-COUNTIFS(B=date,A="Customer query")-COUNTIFS(B=date,A="Management query")-COUNTIFS(B=date,C<date-@)

We found that in some instances a project delivered in the previous week (and therefore subtracted by the last staement) had also hit a query status and so was being subtracted twice. We don't know how long that issue had existed for we only noticed it when someone had a target of -1 pop up on their report. Hence the last two terms is what I think is the solution to this double subtraction (it's referencing cells on other sheets for both sides of the COUNTIFS range/criteria for the bits I've left out to try and keep it legible)

=COUNTIFS('Region 3'!$AE:$AE,J98,'Region 3'!$F:$F,'VALIDATION DPR'!$F$16)-COUNTIFS('Region 3'!$AE:$AE,J98,'Region 3'!$F:$F,'VALIDATION DPR'!$F$16,'Region 3'!$C:$C,'VALIDATION DPR'!$A$24)-COUNTIFS('Region 3'!$AE:$AE,J98,'Region 3'!$F:$F,'VALIDATION DPR'!$F$16,'Region 3'!$C:$C,'VALIDATION DPR'!$A$26)-COUNTIFS('Region 3'!$AF:$AF,"<"&'DPR R3'!J98,'Region 3'!$AE:$AE,'DPR R3'!J98,'Region 3'!$AG:$AG,'VALIDATION DPR'!$F$8,'Region 3'!$F:$F,'VALIDATION DPR'!$F$16)

(that's an earlier version the actual formula without the last two subtractions and some of the other minor changes I made to remove last week's hits from the target more effectively)

User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Re: Pimp my ride (excel version)

Post by crispybits »

I think I just figured it out (sometimes explaining a problem to others helps like that)

COUNTIFS(B=date,A<>"Management query",A<>"Customer query",C<=Monday's date)+COUNTIFS(B=date,A<>"Management query",A<>"Customer query",C=blank)

Rules out the two query statuses from both counts
Only counts projects delivered this week or never delivered yet (rules out projects delivered in previous weeks)

I'm sure I'm missing something but I'll go test it and see what happens

User avatar
Mystery
FISO Knight
Posts: 13816
Joined: 13 Oct 2005, 18:33
Location: Just about here
FS Record: 116th in TFF 05/06
Contact:

Re: Pimp my ride (excel version)

Post by Mystery »

But you mentioned that the project deadline gets reset if the outcome is rejected so the target figures are going to vary according to the acceptance of the project or otherwise.

Once a day has passed its target shouldn't retrospectively change. I.e. this project should have been delivered on Monday so that's it's target date. It was delivered on Monday too, but oh no.. It was rejected on Tuesday and had it's target reset to Friday. Surely that shouldn't affect Monday's target but it would because you overwrite the target date.

I think you need an E = flexed project deadline date, only to be populated where there is a delivered date, but the status isn't Complete.

That makes sense to me anyway, but buying two bottles of Co-op Prosecco did too!

User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Re: Pimp my ride (excel version)

Post by crispybits »

The deadline is always extended into the next week (7 day standard SLA) so thankfully that isn't a problem.

I've thoroughly tested it with various scenarios and it works so I'm gonna stop playing with it for now. The reality is slightly more complicated than the scenario I described (the project can actually be rejected twice and go back for a new deadline both times before the third rejection kills it dead) so I've had to add another column hidden away on the main sheet to give me a "last submitted" date but that's not really an issue, it just means someone has to go into the 6 workbooks once a week and copy down the formula in the hidden column, or I have to retrain everyone that instead of "insert row" they copy the blank row at the bottom and "insert copied cells" to maintain the formula. But better we get accurate figures from it and have a couple of extra clicks to add more rows or 30 seconds maintenance once a week than we constantly have to do long-winded checks and double checks on the report every day.

Thanks for helping though, as I said just having an audience is all you need sometimes, by explaining the problem you clarify it for yourself.

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”