Formula Help (If it's Even Possible To Do)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

It's for a football pool and I told the guy I;d research and play around to
see if it's possible.
The spreadsheet looks like this:
NAME 16 15
Bob Pitts New England
Joe Seattle Minnesota

It goes 16 through 1
Say Bob gets them all right except 15 (which he picked New England to win)
he gets 121 (possible out of 136 points).
Bob is in row 2 and takes up 16 columns. Right now I do the math by hand. Is
there a way to assign a cell a number value even though a team name is
entered in it?
The ideal situation is to check after the games and delete the wrong entries
and then use a SUM at the end. But for that to work a row needs a value I
take it.
I am quite lost on what to do here.
I am open to other ideas as well!
 
You could add the results to another sheet, where basically you would
have one row of 16 columns, with each cell containing the winning
team's name - suppose this occupies cells B2 to Q2 on Sheet2. I presume
that to get 136 points you are adding scores 1 to 16 together, so
assume that these are in B1 to Q1 on Sheet1 You can enter this array*
formula in R2 of Sheet1:

=SUM(IF(B2:Q2=Sheet2!B$2:Q$2,B$1:Q$1,0))

*As this is an array formula, then once you have typed it in (or
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly, then Excel will add curly braces { }
around the formula when viewed in the formula bar - you must not type
these yourself.

The formula can then be copied down to give the scores for the other
players.

Hope this helps.

Pete
 
Jerid B said:
It's for a football pool and I told the guy I;d research and play around
to
see if it's possible.
The spreadsheet looks like this:
NAME 16 15
Bob Pitts New England
Joe Seattle Minnesota

It goes 16 through 1
Say Bob gets them all right except 15 (which he picked New England to win)
he gets 121 (possible out of 136 points).
Bob is in row 2 and takes up 16 columns. Right now I do the math by hand.
Is
there a way to assign a cell a number value even though a team name is
entered in it?
The ideal situation is to check after the games and delete the wrong
entries
and then use a SUM at the end. But for that to work a row needs a value I
take it.
I am quite lost on what to do here.
I am open to other ideas as well!
Click to expand...

Hi Jerid

You can use SUMIF() instead of SUM()
Assuming Headings (NAME 16 15 etc.) in B1:Q1

1. In R2 enter this formula:
=SUMIF(B2:Q2,"<>",$B$1:$Q$1)

2. Copy R2 down with the fill handle
(The little square in the lower right corner of the cell)

The solution assumes that you delete the wrong entries.
 
Leo... THANK YOU!! It works beautifully!!

Leo Heuser said:
Hi Jerid

You can use SUMIF() instead of SUM()
Assuming Headings (NAME 16 15 etc.) in B1:Q1

1. In R2 enter this formula:
=SUMIF(B2:Q2,"<>",$B$1:$Q$1)

2. Copy R2 down with the fill handle
(The little square in the lower right corner of the cell)

The solution assumes that you delete the wrong entries.

--
Best regards
Leo Heuser

Followup to newsgroup only please.
Click to expand...
 
Back
Top