# Anybody any good with excel?

I have a year's worth of data in a spreadsheet that I'd really like to analyse in the form of a line graph - before anybody asks this data wasn't put together by me for this particular task so the spreadsheet wasn't designed to output the specific data I require.

For the sake of an example, lets say the spreadsheet is about usage of meeting rooms (it isn't but the exact nature of the data is confidential so I cannot divulge it).

I have a list of when each meeting room was booked (start time and finish time in separate columns). What I would like to do is to plot a graph with 30 minute intervals on the x axis and the number of meeting rooms in use at that specific time on the y axis. Is this possible or do I need to manually extract further data? Like I say I have a year's worth of data (in weekly sections) so would rather not have to manually do anything.

Any help would be very much appreciated.

For the sake of an example, lets say the spreadsheet is about usage of meeting rooms (it isn't but the exact nature of the data is confidential so I cannot divulge it).

I have a list of when each meeting room was booked (start time and finish time in separate columns). What I would like to do is to plot a graph with 30 minute intervals on the x axis and the number of meeting rooms in use at that specific time on the y axis. Is this possible or do I need to manually extract further data? Like I say I have a year's worth of data (in weekly sections) so would rather not have to manually do anything.

Any help would be very much appreciated.

If you can get around or relax the confidentiality aspect then drop me a message.

I don't think that's going be too help to the op it maybe all there banking details etc. send them a a example spread sheet that maybe raiser for them I'm not close to a pc and not too good on the phone spreadsheets to help so sorry there.

(1) Range D12:F13 is a setup to input the start and end dates that you want visualised on the chart. "No of days" cell is there so that the input of two dates is not required, as the "End Date" is calculated by adding "No of days" to the "Start date". Or you could just enter the 2 dates. Cell D16 is the meeting room identifier that you want to include on the chart.

(2) Range H2:X2 is a simple sum of the values within each column (cell H2 is

=SUM(H3:H8), I2 is =SUM(I3:I8), etc). This is the data that will be displayed on the chart.The following 3 formulae are copied and pasted down the rows in their respective column to populate the table.

(3) This calculates whether the room in cell D16 is applicable to this row.

Cell A3 is

=IF(C3=$D$16,1,0)(4) This calculates whether the date range in range D12:F13 is applicable to this row.

Cell B3 is

=IF(AND(D3>=$D$13,D3<=$E$13),1,0)(5) This calculates whether the individual time is applicable to this row.

Cell H3 is

=IF(AND(H$1>=$E3,H$1<=$F3,$A3=1,$B3=1),1,0)Everything else is literal values that you would take from your data.

Hope this helps.

Edited by:"bud67" 24th JanWe have a number of rooms. For each room we have a start time and finish time. OP simply requires to know whether rooms were in use during certain times of the day. For arguments say let us begin at 08:00 and finish at 20:00. Thirty minute intervals provides us with 24 columns to be labelled. for every row of data regarding the room start/finish times in each column stated we need to use a truth question to determine if the room was occupied or not. If it is, we provide say "Y" and if not we say "N". It is just a case then of totalling the "Y"s in each column and we have something which we can graph.

Should be possible to chart from the date the first data was collected to the last entry or if desired splitting into say months.