Results 1 to 8 of 8
Thread: MS Excel Question
-
07-20-2009, 05:39 PM #1Junior 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?
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?
-
07-20-2009, 05:53 PM #2Web Hosting Master
- Join Date
- Oct 2001
- Location
- Ohio
- Posts
- 8,535
=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.
-
07-20-2009, 07:19 PM #3Junior 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?
-
07-20-2009, 07:34 PM #4Junior 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.
-
07-21-2009, 11:08 AM #5Web Hosting Master
- Join Date
- Jul 2004
- Location
- Memphis, TN
- Posts
- 1,225
Code:=if(istext(A1),1,0)
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.
-
07-21-2009, 09:31 PM #6Junior 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.
-
07-22-2009, 11:01 AM #7Web 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"))
let me know if that works for ya.
-
07-24-2009, 03:33 PM #8Disabled
- Join Date
- Jun 2009
- Location
- Las Vegas, NV
- Posts
- 89
Thanks guys, I was having the same issue yesterday =)
Similar Threads
-
Excel Formula Question
By marsdend in forum Computers and PeripheralsReplies: 0Last Post: 11-28-2008, 01:37 PM -
Excel Question
By -T{H}R- in forum Web Hosting LoungeReplies: 1Last Post: 06-30-2005, 09:40 PM -
Excel Function Question
By simonclark in forum Web Hosting LoungeReplies: 0Last Post: 11-12-2004, 06:49 AM -
MS Excel question
By Ron in forum Web Hosting LoungeReplies: 0Last Post: 10-24-2004, 11:58 PM -
Stupid Excel Question
By Amish_Geek in forum Web Hosting LoungeReplies: 3Last Post: 11-21-2003, 09:08 PM