Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: can anyone please help me???

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have tried methods that i know, but it doesn't work, so i hope there is an expert with this who can help me out with this bit!
    ok, i got two column with these data:

    Round Pos
    1 1
    1 2
    1 3
    1 4
    1 5
    1 6
    1 7
    1 8
    1 9
    1 10
    1 Incompleted
    1 Incompleted
    1 Incompleted
    2 1
    2 2
    2 3
    2 4
    2 5
    2 6
    2 7
    2 8
    2 Incompleted
    2 Incompleted
    2 Incompleted

    I want the number of ppl with a Position (mean every1 except those who incompleted it). for round both round 1 and 2.

    So the experted result is :

    Round 1 10 ppl finished it
    Round 2 8 ppl finished it

    but of course i need to do this excel, can anyone pls teach me how i can do this please?

    please help


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    By the way, please note that the data in such list may not be in order, that mean some result of round 1 could be at the bottom of the result list and some of them could well be at the very top... and please note that i cannot sort this list.

    is there anyway around this still?

    pls help

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-13 14:01, LMF wrote:
    I have tried methods that i know, but it doesn't work, so i hope there is an expert with this who can help me out with this bit!
    ok, i got two column with these data:

    Round Pos
    1 1
    1 2
    1 3
    1 4
    1 5
    1 6
    1 7
    1 8
    1 9
    1 10
    1 Incompleted
    1 Incompleted
    1 Incompleted
    2 1
    2 2
    2 3
    2 4
    2 5
    2 6
    2 7
    2 8
    2 Incompleted
    2 Incompleted
    2 Incompleted

    I want the number of ppl with a Position (mean every1 except those who incompleted it). for round both round 1 and 2.

    So the experted result is :

    Round 1 10 ppl finished it
    Round 2 8 ppl finished it

    but of course i need to do this excel, can anyone pls teach me how i can do this please?

    please help

    [img]/board/images/smiles/icon_frown.gif[/img]
    =COUNTIF(A2:A50,1)-COUNTIF(B2:B50,"Incompleted)

    =COUNTIF(A2:A50,2)-COUNTIF(B2:B50,"Incompleted)

    where A2:A50 houses the round data and B2:B50 the pos data. Adjust to suit,



  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks for helping, but
    it doesn't seem to work because:

    =COUNTIF(A2:A50,1) will be 13

    -COUNTIF(B2:B50,"Incompleted) will be 6

    but actually there are only 3 incompleted in round 1, so this won't work...

    but pls think of any other solution about this please?

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    assuming that the data you're working with is in a range named "Data" try the following:

    in cell F1 enter "Round"
    in cell F3 enter "1"
    in cell G1 enter "Round"
    in cell G3 enter "2"
    # of people finished in round 1 is:
    =DMAX(Data,"Pos",F1:F2)

    # of people finished in round 2 is:
    =DMAX(Data,"Pos",G1:G2)

    It's never too late to learn something new.

    Ricky

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops!

    I meant in F2 enter "1" and in G2 enter "2"

    It's never too late to learn something new.

    Ricky

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-13 14:09, LMF wrote:
    thanks for helping, but
    it doesn't seem to work because:

    =COUNTIF(A2:A50,1) will be 13

    -COUNTIF(B2:B50,"Incompleted) will be 6

    but actually there are only 3 incompleted in round 1, so this won't work...

    but pls think of any other solution about this please?
    You're right. How disturbing!

    =COUNTIF(A2:A50,1)-SUMPRODUCT((A2:A50=1)*(B2:B50="Incompleted"))

    The same logic for Round value of 2.

    Improved version: Since Yogi Anand provided a DCOUNT version, I'll take up just the SUMPRODUCT version, which should be shortened to:

    SUMPRODUCT((A2:A50=1)*(B2:B50<>"Incompleted"))

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-04-13 15:53 ]

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yo, Ricky, the Dmax thing doesn't seem to work when the list is not in order...

    as i said:

    please note that the data in such list may not be in order, that mean some result of round 1 could be at the bottom of the result list and some of them could well be at the very top... and please note that i cannot sort this list.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, your method makes it works.

    thank you very much for your help!



    thanks to Ricky too

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi LMF:
    Try the formula:

    =COUNTIF(B1:B25,">=1")
    this will give you 18 for those who have completed both rounds 1 and 2
    HTH

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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