Results 1 to 4 of 4
  1. #1
    New Lounger
    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$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?
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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$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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)))

  4. #4
    New Lounger
    Join Date
    Oct 2008
    Location
    Bury St Edmunds
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •