1. Junior Guru Wannabe
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?

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. Web Hosting Master
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. Junior Guru Wannabe
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. Junior Guru Wannabe
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. Web Hosting Master
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. Junior Guru Wannabe
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. Web Hosting Master
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. Disabled
Join Date
Jun 2009
Location
Las Vegas, NV
Posts
89
Thanks guys, I was having the same issue yesterday =)

#### Posting Permissions

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