+ Reply to Thread
Results 1 to 5 of 5

Countif Formula between Values from a Data Table

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Question Countif Formula between Values from a Data Table

    Hi,

    In short I am trying to count values coming out from a data table, so that I can break the results down into approximate percentages.

    Basically in H6 to H10005 I have predicted margin of victory.

    Those margins of victory don't vary between +40 and -40 (e.g. an extreme result for one side or the other);

    In cells C11 to C91 I have listed the margins -40 through to +40 (with C11 being -40).

    To break the margins of victory down (e.g. for -40) I am looking to count the number of times a score between -40.5 and -39.5 occurs.

    To do this in D11 I have gone for the following formula - but I have obviously made an error somewhere (as when copied through I am not getting anything counted from the 10000 results):

    =COUNTIF($H$6:$H$10005,"(>=(C11-0.5))"-COUNTIF($H$6:$H$10005,"(<=(C11+0.5))"))

    I'm struggling to see where I went wrong as the range is good and I thought the criteria was okay (e.g. if C11 is greater than or equal to -40.5 minus if C11 is less than or equal to -39.5). I'm sure I've got something basic the wrong way round so if anyone has any advice I'd be grateful.

    Thanks in advance,
    Last edited by mrvp; 03-06-2012 at 06:46 PM. Reason: margin of victory - not scores

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Countif Formula between Values from a Data Table

    Try: =COUNTIF($H$6:$H$10005,">="&(C11-0.5))-COUNTIF($H$6:$H$10005,"<="&(C11+0.5))

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Countif Formula between Values from a Data Table

    Thanks - at least I now know the convention for putting something like this together (I didn't figure the "&"). Now I need to just check what was wrong with the formula, as I've obviously made a mistake somewhere as all of the cells have come out as being 9998. I've expanded the range of margins of victory to -50 to +50 and I still have the same result so I've obviously not thought this through fully yet.
    Last edited by mrvp; 03-06-2012 at 06:56 PM. Reason: missed a now out - first sentance

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Countif Formula between Values from a Data Table

    Try to change "<=" to ">" in 2nd COUNTIF
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Countif Formula between Values from a Data Table

    Thanks. I am going to go away and just work this through. It's one or the other - the data table is being slow to work through at the moment so I'm going to let it do it's thing and will post something back tomorrow.
    Last edited by mrvp; 03-06-2012 at 07:25 PM. Reason: typo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1