# Displaying number of occurrences a day in line graph

#### Aneirin

##### New Member
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.

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.

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.

Excellent. Glad to hear that it helped you out.

Replies
2
Views
158
Replies
0
Views
142
Replies
16
Views
307
Replies
2
Views
234
Replies
3
Views
394

1,214,714
Messages
6,121,043
Members
449,006
Latest member
cthorne1

### 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.

### Which adblocker are you using?

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

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