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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,954
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,806
Members
414,103
Latest member
imamalidadashzada

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