Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    45

    MS Excel Question

    Is there a way to assign a value to a cell that will only be included if the cell is filled?

    I want to create a spreadsheet for the NCAA basketball tournament.

    The sheet would basically look like the brackets you always see. The first column on both the left and right sides would have the names of the teams who make the tournament and would have a value of zero.

    The cells in each successive column would be worth one point more. For instance if a team wins game one they will have a value of one point. If they win their second game they will have a value of 3 points.

    Points will only be tallied if a team name is inserted in the cell.

    Essentially what will happen is a person will fill in their pool and have the maximum amount of points.
    As the tournament progresses points will be deducted as teams are eliminated. The tally will be a cell below the brackets that will be the sum of all cells that are filled.

    I'm not sure if I'm explaining this well but are there any Excel gurus out there that have any suggestions?

  2. #2
    Join Date
    Oct 2001
    Location
    Ohio
    Posts
    8,299
    =IF(A2>B2,A1,B1)

    A2 is the points of Team 1, B2 is the points of Team 2. The equation is saying if Team 1 has more points than Team 2, then spit out the text in column A1. If the equation is false, then spit out the text in B1.

  3. #3
    Join Date
    Jul 2009
    Posts
    45
    Not sure that's what I'm looking for. Let me put it this way.
    If I want a cell to equal one point but only if the cell has a word in it, how would I do that.
    So if I have 32 cells and 25 have the name of a winning teams and 7 blank cells I want to add the total of cells in that column so that only the cells that are filled will be included.
    In this case if I do the sum of cells 1 to 32 the total would be 25 because only 25 cells have text in them.

    Is this possible? To make a cell have a value of one based on whether or not it has text in it?

  4. #4
    Join Date
    Jul 2009
    Posts
    45
    I think this requires an "if" command but I have not idea how to do it.

    For instance, IF cell B1 = Text than the value assigned to that cell would be 1.

  5. #5
    Join Date
    Jul 2004
    Location
    Memphis, TN
    Posts
    1,225
    Code:
    =if(istext(A1),1,0)
    That should do it..

    A1 being whatever cell your checking..if that cell contains text, the value is 1..else 0.


    You can substitute the 1 and 0 as well for any other number or text as well..if you put in text instead of a number in the formula..make sure you use "" to declare the text.

    For example:
    Code:
     =if(istext(A1),"win","loss")
    Last edited by cheyenne1212; 07-21-2009 at 11:23 AM.

  6. #6
    Join Date
    Jul 2009
    Posts
    45
    Thank you for that Cheyenne.
    Now, to make things more difficult, can you do this and still have the original text show up in the cell but have the value added to a sum total in another cell?
    Let's say game one in the first round is Florida/Michigan. If a player chooses Michigan and Michigan wins, then Michigan would show in that cell in a second column, but that same cell would have a value of 1 if doing a sum of all cells with text.

  7. #7
    Join Date
    Jul 2004
    Location
    Memphis, TN
    Posts
    1,225
    Alright..try this one:

    This might be another way to accomplish this and auto increment a cell.

    Code:
    =IF(A1="","",TEXT(COUNTA($A1:$A40),"0"))
    This formula counts the number of cells in a column / range that are not blank..then increments whatever cell you put this formula in by one..so initially the cell is 0..then say you have 10 cells and 5 of them are empty, the new number in that cell will be 4.

    let me know if that works for ya.

  8. #8
    Join Date
    Jun 2009
    Location
    Las Vegas, NV
    Posts
    89
    Thanks guys, I was having the same issue yesterday =)

Similar Threads

  1. Excel Formula Question
    By marsdend in forum Computers and Peripherals
    Replies: 0
    Last Post: 11-28-2008, 01:37 PM
  2. Excel Question
    By -T{H}R- in forum Web Hosting Lounge
    Replies: 1
    Last Post: 06-30-2005, 09:40 PM
  3. Excel Function Question
    By simonclark in forum Web Hosting Lounge
    Replies: 0
    Last Post: 11-12-2004, 06:49 AM
  4. MS Excel question
    By Ron in forum Web Hosting Lounge
    Replies: 0
    Last Post: 10-24-2004, 11:58 PM
  5. Stupid Excel Question
    By Amish_Geek in forum Web Hosting Lounge
    Replies: 3
    Last Post: 11-21-2003, 09:08 PM

Posting Permissions

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