narrowdave
New Member
- Joined
- May 3, 2017
- Messages
- 19
Hi – first posting on here, so hopefully I give the right level of detail… I seem to need some help I’ve set up a spreadsheet to monitor numerous aspects of admissions and discharges from mental health hospitals across a UK region. Data is entered into an excel table – there are loads of connected pivot charts which are hidden and then loads of the data is brought together and shown via a dashboard type setup…
All’s fine – I’m a nurse rather than a techie, so much learning has occurred… I have one remaining challenge however… I need to be able to show readmission data. i.e. number of readmissions within a 30 day period (since recording began); number of readmissions within the last 30 days (i.e. ending today); number of readmissions within a 365 day period (since data collection began); and number of readmissions within the last 365 day period (i.e. ending today)…
Once in use the spreadsheet will rapidly grow in terms of number of patients being monitored and users of the system may need to access real time data at any point in time.
My spreadsheet is formatted as follows (dummy data included AND only showing the columns relevant to current query):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD]Z[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]Unique Patient Identifier[/TD]
[TD]Admission Date[/TD]
[TD]Discharge Date[/TD]
[TD]Admission number[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]265 409 8111[/TD]
[TD]12/12/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]123 456 7890[/TD]
[TD]25/01/2017[/TD]
[TD]31/01/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]123 765 8976[/TD]
[TD]12/02/2017[/TD]
[TD]28/02/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]123 456 7890[/TD]
[TD]09/03/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]
As I said, the number of rows will grow rapidly…
So what I need are some formulae which will:
I’m competent but not good at writing formulae (getting better though!!). I would appreciate any suggestions, advice, assistance that people can offer…
Thx
All’s fine – I’m a nurse rather than a techie, so much learning has occurred… I have one remaining challenge however… I need to be able to show readmission data. i.e. number of readmissions within a 30 day period (since recording began); number of readmissions within the last 30 days (i.e. ending today); number of readmissions within a 365 day period (since data collection began); and number of readmissions within the last 365 day period (i.e. ending today)…
Once in use the spreadsheet will rapidly grow in terms of number of patients being monitored and users of the system may need to access real time data at any point in time.
My spreadsheet is formatted as follows (dummy data included AND only showing the columns relevant to current query):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD]Z[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]Unique Patient Identifier[/TD]
[TD]Admission Date[/TD]
[TD]Discharge Date[/TD]
[TD]Admission number[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]265 409 8111[/TD]
[TD]12/12/2017[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]123 456 7890[/TD]
[TD]25/01/2017[/TD]
[TD]31/01/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]123 765 8976[/TD]
[TD]12/02/2017[/TD]
[TD]28/02/2017[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]123 456 7890[/TD]
[TD]09/03/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]
As I said, the number of rows will grow rapidly…
So what I need are some formulae which will:
- identify duplicate Unique Patient Identifiers – i.e. pick out readmissions
- then compare measure the interval between admission date and previous discharge dates for those patients (of course some patients will still be in hospital so have no discharge date – for others there may be no readmissions i.e. it's their first admission).
- Then tell me how many readmissions occurred within:
- 30 days ending on the day of the query
- 365 days ending on the day of the query
- Any 30 day interval since recording began
- Any 365 day interval since recording began
I’m competent but not good at writing formulae (getting better though!!). I would appreciate any suggestions, advice, assistance that people can offer…
Thx
