1. Complicated formulas! (2003)

On the attached spreadsheet i am trying to calculate an average wait, by category, by age range (however the age range hasnt been defined and gets defined in the formula....
This is how i am calculating the age range at the moment by category and i need to add in the average wait now, but im lost on where to put it...
=SUMPRODUCT((((ROUNDDOWN((Data!\$A\$2:\$A\$5001-Data!\$K\$2:\$K\$5001)/365,0))>=\$A62)*(((ROUNDDOWN((Data!\$A\$2:\$A\$5001-Data!\$K\$2:\$K\$5001)/365,0))<=\$B62)*(Data!\$J\$2:\$J\$5001=\$D\$60)*NOT(ISBLA NK(Data!\$A\$2:\$A\$5001)))))

Any ideas?

2. Re: Complicated formulas! (2003)

Why not replace the "ROUNDDOWN((Data!\$A\$2:\$A\$5001-Data!\$K\$2:\$K\$5001)/365,0)" with the intermediate values in Data!\$L\$2:\$L\$5001?

Something like the array (confirm with ctrl-shift-enter)
=AVERAGE(IF((Data!\$L\$2:\$L\$5001>=\$A5)*(Data!\$L\$2:\$L \$5001<=\$B5)*(Data!\$J\$2:\$J\$5001=\$D\$60)*NOT(ISBLANK( Data!\$A\$2:\$A\$5001)),Data!\$I\$2:\$I\$5001))

[But I am confused about the category columns and where they are located in the data, and the fact that nothing is in D60......]

Steve
PS. Perhaps this (I got rid of the D60 check and used triage category (col to match the item in row 3...
=AVERAGE(IF((Data!\$L\$2:\$L\$5001>=\$A5)*(Data!\$L\$2:\$L \$5001<=\$B5)*(Data!\$B\$2:\$B\$5001=D\$3)*NOT(ISBLANK(Da ta!\$A\$2:\$A\$5001)),Data!\$I\$2:\$I\$5001))

3. Re: Complicated formulas! (2003)

Enter the following formula in Data!L2:

=DATEDIF(K2,A2,"y")

and fill down.

You can use this array formula (confirm with Ctrl+Shift+Enter) in Sheet1!D5:

=AVERAGE(IF((Data!\$L\$2:\$L\$5001>=\$A5)*(Data!\$L\$2:\$L \$5001<=\$B5)*(Data!\$J\$2:\$J\$5001=\$D\$60)*NOT(ISBLANK( Data!\$A\$2:\$A\$5001))*(Data!\$B\$2:\$B\$5001=D\$3),Data!\$ I\$2:\$I\$5001))

Fill down, then right. If you want to suppress the #DIV/0 errors:

=IF(ISERROR(AVERAGE(IF((Data!\$L\$2:\$L\$5001>=\$A5)*(D ata!\$L\$2:\$L\$5001<=\$B5)*(Data!\$J\$2:\$J\$5001=\$D\$60)*N OT(ISBLANK(Data!\$A\$2:\$A\$5001))*(Data!\$B\$2:\$B\$5001= D\$3),Data!\$I\$2:\$I\$5001))),"-",AVERAGE(IF((Data!\$L\$2:\$L\$5001>=\$A5)*(Data!\$L\$2:\$ L\$5001<=\$B5)*(Data!\$J\$2:\$J\$5001=\$D\$60)*NOT(ISBLANK (Data!\$A\$2:\$A\$5001))*(Data!\$B\$2:\$B\$5001=D\$3),Data! \$I\$2:\$I\$5001)))

4. Re: Complicated formulas! (2003)

Thanks Hans

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•