Results 1 to 4 of 4
Thread: Complicated formulas! (2003)

20081027, 23:05 #1
 Join Date
 Oct 2008
 Location
 Bury St Edmunds
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
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$5001Data!$K$2:$K$5001)/365,0))>=$A62)*(((ROUNDDOWN((Data!$A$2:$A$5001Data!$K$2:$K$5001)/365,0))<=$B62)*(Data!$J$2:$J$5001=$D$60)*NOT(ISBLA NK(Data!$A$2:$A$5001)))))
Any ideas?

20081028, 00:23 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Complicated formulas! (2003)
Why not replace the "ROUNDDOWN((Data!$A$2:$A$5001Data!$K$2:$K$5001)/365,0)" with the intermediate values in Data!$L$2:$L$5001?
Something like the array (confirm with ctrlshiftenter)
=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))

20081028, 00:25 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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)))

20081028, 01:46 #4
 Join Date
 Oct 2008
 Location
 Bury St Edmunds
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Complicated formulas! (2003)
Thanks Hans