Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Help (If it's Even Possible To Do)
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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Help (If it's Even Possible To Do)
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 wrote: 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! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Help (If it's Even Possible To Do)
"Jerid B" skrev i en meddelelse
... 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. -- Best regards Leo Heuser Followup to newsgroup only please. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Help (If it's Even Possible To Do)
Leo... THANK YOU!! It works beautifully!!
"Leo Heuser" wrote: "Jerid B" skrev i en meddelelse ... 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. -- Best regards Leo Heuser Followup to newsgroup only please. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula Help (If it's Even Possible To Do)
Glad I could help you. Jerid.
Thanks for the feedback! Leo Heuser "Jerid B" skrev i en meddelelse ... Leo... THANK YOU!! It works beautifully!! "Leo Heuser" wrote: "Jerid B" skrev i en meddelelse ... 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. -- Best regards Leo Heuser Followup to newsgroup only please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |