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

[Jerid B]

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!

 

[Pete_UK]

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

 

[Leo Heuser]

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!

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.

 

[Jerid B]

Leo... THANK YOU!! It works beautifully!!

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.

 

[Leo Heuser]

Glad I could help you. Jerid.
Thanks for the feedback!

Leo Heuser