Hi there,
I am both new to the forum and also to Excel but have been asked to analyse a database as part of my new role. For a bit of context I work in a health service and I am trying to find out is the average of those who have completed treatment. The database is set up to record this in different ways (as outlined below) and I need the formula to consider these different variables in order to work out the average.
The array formula I have researched and tried to run so far is =AVERAGE(IF(A_treatment="complete",IF(REFDIS!$T$3:$T$14=1,IF(A_N1to1>0,A_N1to1))))
A_treatment refers to the clients treatment status so if it is marked as "complete" or "incomplete"
REFDIS(the name of the excel spreadsheet) refers to the column of the database which marks whether someone is receiving 1:1 treatment (marked as 1) or not (marked as 0)
A_N1to1 refers to number of sessions the client has had. The database has been set up to automatically change a clients treatment status from incomplete to complete once they have had 4 or more sessions.
I am working with a small database at the moment (only 12 patients-T3 to T14 in my database) so i can try and familiarise myself with the formulas...I have never run an array formula before so apologies if this post is very basic or if i am not explaining myself well. From what I can see the formula is referring to the right columns on the database (and i am pressing ctrl, shift, enter) yet the division error keeps coming up. I can only think that somehow I haven't defined what to calculate the average from?
I am at a bit of a loss and Google is not turning up anything really so any help would be much appreciated.
Many thanks
Bookmarks