Displaying number of occurrences a day in line graph

Aneirin

New Member
Joined
Aug 28, 2014
Messages
13
Hello. I appreciate that this may be pressing the limits of what excel can do and may not be possible.

What I want to do is as follows:-

I have a spread sheet, and on this spread sheet it has the dates that an item entered a particular unit of storage, and when it left a particular unit of storage.

So the spreadsheet looks like this

Coloumn F Coloumn U Column V Column Z
Name of item Date entered Date left Unit of storage
Item A 01/04/2012 01/07/2012 Unit 1
Item B 05/06/2012 30/06/2012 Unit 2
Item C 01/05/2012 01/08/2012 Unit 1
Item D 01/05/2012 14/05/2012 Unit 3
Item D 15/05/2012 01/08/2012 Unit 1

The items keep on going down, (a lot of columns have been missed out as they include information we need, but not for this purpose) there are a good couple of hundred items and some of them appear multiple times (as we still hold the, they are just transferred from one unit of storage to another)

What I want, is to try and work out how many items are in a particular unit on a certain date and display it on a line graph. (though I appreciate it may be much easier to do t on mulitple graphs, with a grap for each unit, there are a small number of units in total, around 8)

i.e.

The X axis of the line graph would be the date going forward on a daily basis
The Y axis of the line graph would be the amount of items
The colour of the line would dictate which unit of storage it was unless using seperate graphs for each unit

That way, I could put a horizontal line on the graph (this horizontal line would mark the maximum number of units that we should be storing) so I can see at which points we are exceeding maximum storage.

The above point may be why it is better to use different graphs as each unit has a different storage capacity and as such the graph could look quite messy if all the lines are stuck on one.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,171
Create a pivot table counting items via Unit, and in the REPORT FILTER put the DAY.
This way you can filter on any particular day.
Chart this data.
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
The explanation of what I was trying to do was going to be too complicated, so I uploaded a sample file here...

https://www.dropbox.com/s/gfcar6yx9l3j1au/Units in Storage.xlsx?dl=0

You can see I've added a chart that has 8 lines for 8 storage units and marks the over capacity times with red plus signs.

The data will update automatically, however, the formulas in 'Data by Date'!B3:I368 will need to be updated to reference the full range in the Data tab (currently it is set to only the 5 rows of data you provided.

I also only added the OverCapacity indicators for Unit 1, but you could easily copy what I did for the other units.
 

Aneirin

New Member
Joined
Aug 28, 2014
Messages
13
The explanation of what I was trying to do was going to be too complicated, so I uploaded a sample file here...

https://www.dropbox.com/s/gfcar6yx9l3j1au/Units in Storage.xlsx?dl=0

You can see I've added a chart that has 8 lines for 8 storage units and marks the over capacity times with red plus signs.

The data will update automatically, however, the formulas in 'Data by Date'!B3:I368 will need to be updated to reference the full range in the Data tab (currently it is set to only the 5 rows of data you provided.

I also only added the OverCapacity indicators for Unit 1, but you could easily copy what I did for the other units.

Thanks for that, exceptionally awesome, managed to get exactyl what I wanted using this.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,511
Messages
5,832,175
Members
430,114
Latest member
kefier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top