Anybody any good with excel?

7 replies
Found 23rd Jan
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.

  1. Ask
  1. Ask

I'd be happy to have a look at it for you, but it would take too long to talk you through how to do it yourself.

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


I'd be happy to have a look at it for you, but it would take too long to … I'd be happy to have a look at it for you, but it would take too long to talk you through how to do it yourself. 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.

I have been able to get some tutorials on excel from you tube. Maybe take a look there on how to generate graphs from data?

Sounds like you have a lot of data to summarise there. The x-axis isn't going to stretch for 365 with 30min intervals, so you probably want to try a week or even a days worth of data for a start. If you have all the data points that you need on the graph already calculate then it could be as simple as hiding all the columns and row that you don't need, selecting the whole table and making a chart from that. But since you have start and end times for each room it's probably not as simple as that and you'll need a calculated field to work out how many rooms are occupied at a specific time of day(30min, interval). Sorry I can't be more helpful.

As ever with Excel, there is more than one way to do this. There's a simple solution to this which may be applicable, depending on how much data you have. You may have to prepare and clean your data first before this will work.

(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 Jan

I cant find anything in the OP that explicitly mentions dates (although I acknowledge that may exist). Neither is it stated explicitly that dates need to be included in the output.
We 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.
Post a comment
    Top Discussions
    1. New bladerunner1959
    2. Xbox One - Buying from Other Regions *WITH* A VPN (Virtual Private Network)70562
    3. The HUKD home for Star Trek: Discovery2685
    4. Add one, leave one game54415416

    See more discussions