Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Conditional Array (2000 SR2)

    Hi, I'm looking for an array formula that will count people whose hourly rate is about to change.

    Post_No___Cur_Hrly______Grade
    1_________5.20__________A
    2_________5.80__________A
    3_________5.30__________C

    Count the number of posts whose grade is "A" and whose current hourly rate is between 5.00 and 5.49

    So far I have {=COUNT(IF(AND(C2:C4="A",AND(B2:B4>=5.00,B2:B4<=5. 49)),A2:A4))}

    But I'm getting some strange answers.

    Thanks,
    Jim MacLeod
    Shetland Isles

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Array (2000 SR2)

    Are you needing the answer in a cell to use in another calculation? If not...it would be just as effective to count the results of a filter.
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Conditional Array (2000 SR2)

    Yes, I need to show the effect of a pay model on numbers of employees. What I'll end up with is:

    _____________________Currently___Currently__Curren tly
    New Grade ___New rate__5.00-5.49__5.50-5.99__6.00-6.49
    A___________4.8__________3________7________0
    B___________5.1__________4________4________0
    C___________5.3__________3________2________2
    D___________5.5__________5________4________3

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Array (2000 SR2)

    Try

    =SUMPRODUCT((B2:B4>=5)*(B2:B4<=5.49)*(C2:C4="A"))

    as a normal formula, or

    =SUM((B2:B4>=5)*(B2:B4<=5.49)*(C2:C4="A"))

    as an array formula.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Array (2000 SR2)

    Try this: =COUNT(IF(AND(C2:C4="A"),AND(B2:B4>=5,B2:B4<=5.49) ,A2:A4))

    Confirm entry by pressing CNTRL + SHIFT. (Do not type the curly braces in!)
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Array (2000 SR2)

    Yes: =SUM((B2:B4>=5)*(B2:B4<=5.49)*(C2:C4="A")) works well.

    PS: Ignore my reply! The more I tweeked it the more I messed it up!!
    Regards,
    Rudi

  7. #7
    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: Conditional Array (2000 SR2)

    <P ID="edit" class=small>(Edited by sdckapr on 26-Sep-05 11:40. Forgot to include last column of table)</P><table border=1><td></td><td align=center>G</td><td align=center>H</td><td align=center>I</td><td align=center>J</td><td align=center>K</td><td align=center>L</td><tr><td align=center valign=bottom>1</td><td align=right valign=bottom>

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Conditional Array (2000 SR2)

    Excellent, thanks for all your help

Posting Permissions

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