Excel helppp

[Flibb]

Doing a project that's looking at time taken to do stuff, and I am trying to come up with a simple way of working out how much work in progress there has been laying around.

Here is a simplified version of the data.

ID	Items	Start	Finish
1	1	10-03-2016	10-04-2016
2	1	10-03-2016	28-03-2016
3	3	10-03-2016	20-03-2016
4	2	01-04-2016	12-04-2016

What I would like to do is calculate how much WIP is laying around. I have a chart that does it graphically, but ideally would like to be able to show a table with the average WIP per month. Any ideas??

[EndlessWaves]

The first thing to do is to get it clear what exactly you want. How is 'WIP laying around each month' defined? The work that has a start date that month but an end date after the end of the month? Items that have no end date yet?

[Phage]

As per Endless.
What units are you working in GBP or Hours ? If hours, I'm sure that there's a formula to subtract one date from another to get days. Days*8 = Hours ?
Hours*Unit Cost= WIP

[Zak33]

Flibb... may I suggest a different chart?

OK, set heading like this
ID
ITEMS
START
FINISH
OWNER
ROADBLOCK
ANTICIPATED COMPLETION
NOTES

now, make some default cells to fill each one with, such as a RED roadblock cell and a GREEN progressing and an ORANGE Slowed
Make default initials for the owners of each project, so Za for Zak and Fl for Flib

the idea is that, you can filter on anything, and see who's in charge or when it's due

you can filter in date, or road block for example

I know I'm an admin but I have no idea how to put an excel chart into this thread.

[Zak33]

This is a horribly simple one


Add more columns if needed.
Add more projects as they arrive.
Filter on the progress or the date, or the owner, or whatever

Manipulate it to work for you

the Progress blocks on the right are to be Copy/Pasted into the progress, and the same should be done for Owners of projects.
In short, keep it under control so other people have to use your format.

And then you can use a pivot table to see it as it grows and keeping it vertical like this also allows vlookup's if you can add job numbers or unique identifiers that other systems use.

[herulach]

This is a horribly simple one
[img.]http://s9.postimg.org/w9oj2p4bj/Tracker.jpg[/img]

Add more columns if needed.
Add more projects as they arrive.
Filter on the progress or the date, or the owner, or whatever

Manipulate it to work for you

the Progress blocks on the right are to be Copy/Pasted into the progress, and the same should be done for Owners of projects.
In short, keep it under control so other people have to use your format.

And then you can use a pivot table to see it as it grows and keeping it vertical like this also allows vlookup's if you can add job numbers or unique identifiers that other systems use.

I'd strongly suggest that if you're exposing it to other people, and using this type of idea, you use cell value filters and conditional formatting on any cells you want to be able to filter on. 100% gtee that form would have Slowish, Roadblock, OffTrack, Fine etc in the status box as soon as it hit a user!

Based on the data you've provided I'm really not sure what you're asking, but if you want the average time to complete an item you'd do something like
=sumproduct(items*(start-finish))/sum(items)

if you want number of items over a month end it'd be
{=SUMIF(items,MONTH(start)<>MONTH(finish))}

You'd hit ctrl, shift, enter for the last one to get it to calculate as an array formula

[Flibb]

The idea of the spreadsheet is to work out the average number of units that are being worked on at any one time each month. I have filters and formulae working to show how long each item takes to complete, I can plot it on calendar, but what I want to be able to work out is the average WIP for the month.
So on the simple data above there were
5 items being worked on 10/3 - 20/3
20/3 to 28/3 where it decreased to 2,
28/3 to 1/4 there were 1 items being worked on,
1/4 to 10/4 its 3 items
10/4-12/4 its 2.

The average comes out at around 3.25 for the 32 days. Or there abouts.

And cheers for looking in on this thread will have a play with the sheet tomorrow.

[herulach]

You could do something with the weighted average, but I'd guess a pivot table is more useful to you, you can put calculated formulae in them then average those. Overlapping dates are going to be the most troublesome at a guess you're going to end up using either the start or end date, or a midpoint to define your cutoffs. Over a long enough period (assuming things are pretty homogenous) things will average out, but you would get edge effects introduced.

[Zak33]

if you pivot using dates along the x horizontal axis, and shrink the columns, you'll start to see a pattern.. which is often better than a plain graph. It'll be like a scatter chart.

so while not mathematical, it could form a "dashboard" where you see hot spots.