Toggle sidebar Toggle sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

Excel Question

Z

zellin

macrumors regular
Original poster
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!
 
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.
 
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
 
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
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back