Excel Question

Discussion in 'Mac Apps and Mac App Store' started by zellin, Feb 3, 2005.

  1. zellin macrumors regular

    Joined:
    Nov 23, 2003
    Location:
    Phoenix, AZ
    #1
    Hey guys,
    I am making an Excel (2004) spreadsheet to keep track of an office pool in which the participants pick sides on various Super Bowl Prop Bets. For example, in cell A3 it says "SBP v AI" which represents a bet. In cells C3, D3, etc. it says which side that the person is taking (either "SBP" or "AI"). I was wondering how I can tell Excel that if the text in B3 (which is saved for the actual result) matches the text in C3, then to add 1 point to a certain cell, say C45, and then also check cells D3, E3, etc and add points to D45, E45, etc. I'm pretty sure there is a function for this because somebody in another office pool did something similar last year for March Madness. Then, also, if there is some way to also calculate the percent correct, disregarding cells which the person left the answer blank. I'm not so sure about that one. Thank you guys very much!
     
  2. h0e0h macrumors 6502a

    h0e0h

    Joined:
    Aug 30, 2004
    Location:
    West Monroe, Louisiana
    #2
    are you familiar w/ Excel @ all? I mean, do you know the basic functions? For example, to get the Average of Cells A4, A5, A6, A7, and A8 to display in Cell A12 you would click on Cell A12 and enter this exactly "=(A4+A5+A6+A7+A8)/5" and the average would be displayed. Hope this helps... let me know.
     
  3. zellin thread starter macrumors regular

    Joined:
    Nov 23, 2003
    Location:
    Phoenix, AZ
    #3
    Yes, I know that, but that doesn't apply to this situation.
     
  4. h0e0h macrumors 6502a

    h0e0h

    Joined:
    Aug 30, 2004
    Location:
    West Monroe, Louisiana
    #4
    well, good luck... i hope that you find what you are lookin for.
     
  5. Frank (Atlanta) macrumors regular

    Joined:
    Oct 29, 2004
    #5
    Logical Argument Statement

    Think you want to look at an "if" statement:

    =if(a1="SBP",<true>,<false>)

    Put the operation in <true> if the statement's true; same for false.

    You can do nested if statements and use different tests, etc. to capture different options.

    Take a look at Insert, Functions, Logical (assuming these are the correct menu items - I'm at work and only have access to Windows Excel...).

    <edit>
    You might even be able to do something a bit more elegant; however, I think I see your approach:

    1. I'd create 2 tables:
    • Table 1 = table of actual results (your column B) and table of participant choices (columns C+). For no choices/bets, I'd input the text "N/A".
    • Table 2 = table of if statements whereby you can calculate point values by participant and sum across or down. You can then also screen for "N/A" choices in Table 1.

    Good luck,
    Frank
     
  6. Applespider macrumors G4

    Applespider

    Joined:
    Jan 20, 2004
    Location:
    looking through rose-tinted spectacles...
    #6
    Formula to check b3 and c3 matched and then to add 1 to the total in C45 would be

    =if(b3=c3,c45+1,c45)
    if you put in $ anchors ($b3=$c3),you can just drag that across to cover d45, e45. Tho how well that works will depend on how you set up the data. I'm puzzled as to why you are working your figures down the sheet.

    I'd set up all the participants in column A, their win pick in column B, their touchdown pick in column C, their total points in column D etc.

    Row 1 is the titles
    Row 2 is the Actuals to be filled in.
    Rows 3 - x are the names of the participants.

    Then your formula would be, assuming 1 point for each pick and starting in the first column after the options.

    if(B3=b$2),1,0) - next column, (if c3=c$2),1,0)

    Those formulas would drag down the list of names and you could add a Sum field in the final column
     

Share This Page