An Excel formula problem

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Optional
    Administrator
    • 06-10-10
    • 61396

    #1
    An Excel formula problem
    Hoping someone can easily see what I am doing wrong here, as I've read the help files and a bunch of tutorial sites 14 times and am tearing my hair out.

    I have a sheet with my soccer bet results in it.

    Column B contains a list of Countries.
    Column M contains a text label for the result. (WIN,LOSS.PUSH, ½P½W or ½P½L)

    I'm trying to make a tally of Win-Loss-Push by country.

    Here's the latest version of the formula, which doesn't work.


    =SUM(IF($B$1:$B$1000="England",IF($M$1:$ M$1000="WIN",1,IF($M$1:$M$1000="½P½W", 0.5,0)),0))

    I'm holding Ctrl+Shift when I press Enter.

    All I get is a 0 or 1 result, depending on the country chosen.


    I'm trying to say If the country is England and the outcome is WIN, count 1, and if the outcome is ½P½W count .5... and give me the sum of the results.

    Any help would be greatly appreciated.
    .
  • djiddish98
    SBR Sharp
    • 11-13-09
    • 345

    #2
    Try working with a pivot table.

    I think if you just put a formula in column N with a conversion from outcome to number, regardless of country (your second two if statements), then run a pivot with the country as a row label and the numbered outcome as a value (make sure to switch to sum as opposed to count), you should get the desired result.
    Comment
    • That Foreign Guy
      SBR Sharp
      • 07-18-10
      • 432

      #3
      Column N - "=if(Mx="Win",1,if(Mx="½P½W",0.5,0 )) where x is the row number

      Wins by country is then as follows

      =Sumif(B:B,="England",N:N) where England is the country name.

      That should do it.
      Comment
      • Optional
        Administrator
        • 06-10-10
        • 61396

        #4
        Thanks guys.

        Not sure it's the most elegant solution, but the second idea was so simple I feel like a dummy for not thinking of it myself. And it worked just as a wanted.
        .
        Comment
        • Optional
          Administrator
          • 06-10-10
          • 61396

          #5
          Just in case anyone else happens along with the same problem, I found the 'more elegant' solution I was looking for;

          =SUM((B1:B1000="England")*(M1:M1000="WIN "))

          Ctrl+Shift+Enter when entering it to make an array formula.

          For some reason it won't work if I just try and specify the range as B:B, without row numbers. If anyone knows how I can make it work for an entire column, please let me know.
          .
          Comment
          SBR Contests
          Collapse
          Top-Rated US Sportsbooks
          Collapse
          Working...