Array formula and the #DIV/0 error

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

Hi Ron,

Thank you for your help and reply.

Yes A_treatment and A_N1to1 are named ranges but they are also the same size as the named range within REFDIS.
Interestingly I just tried your code and it came out with the same error...

Do you have any further thoughts about what might be causing this?

Thanks

Could you post a sample file as this will help in diagnosing the problem.

Click "Go Advanced" then the "paper clip" icon to upload a file.

Hi,

Please see attached dataset....this is a copy of what I have been working with to get used to excel and various formulas.

Thanks

What cell is the formula in? I tried Ron's formula and had a result of 4.

What is the expected result?

Hi John,

Thank you-I just retried Ron's formula and I also got an answer of 4...not sure why i didn't earlier.

With a bigger dataset would it be ok to continue using this formula do you think? it would make life easier!

Thank you all for looking through and helping me-it is much appreciated.

Yes ... continue with Ron's formula.

If all is OK can you mark the thread as SOLVED - "Thread Tools" on first posting

Thank you.