Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help setting up and array formula in Excel 2003/XP
I have a football pool and I'm trying to tally the correct picks. Format is
as follows: Da Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK JAX IND TEN BAL TB 52 Correct picks below: OAK CHI IND TEN WAS CAR The last column is the tie-breaker team and the points that you think both teams will score. You win if the score is the same or higher i.e. combined score is 51, you predicted 44 but another entrant predicted 49. He wins because his score is higher than yours. If you can't help me with the tie-breaker that's ok. Really need an array to tally correct picks. Thanks in Advance for any assistance. db |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help setting up and array formula in Excel 2003/XP
List the weekly winners across the row at the top of the sheet:
............A........B.....C...D....E 1...Winners Oak Chi Ind Ten 2...Da Boss SD CHI IND TEN 3...Al Davis OAK JAX IND TEN Then use a formula like this: =SUMPRODUCT(--(B$1:E$1=B2:E2)) Copy down as needed. As far as the tie breaker....you'd need to define *all* the rules that apply. For example: The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? -- Biff Microsoft Excel MVP "snax626" wrote in message ... I have a football pool and I'm trying to tally the correct picks. Format is as follows: Da Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK JAX IND TEN BAL TB 52 Correct picks below: OAK CHI IND TEN WAS CAR The last column is the tie-breaker team and the points that you think both teams will score. You win if the score is the same or higher i.e. combined score is 51, you predicted 44 but another entrant predicted 49. He wins because his score is higher than yours. If you can't help me with the tie-breaker that's ok. Really need an array to tally correct picks. Thanks in Advance for any assistance. db |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help setting up an array formula in Excel 2003/XP
Thanks for your quick response. I setup the SUMPRODUCT function and I'm
getting an error. It's something I'm doing wrong naturally. =SUMPRODUCT(--(B$1:E$1=B2:E2)) what goes here? ^ Winners OAK CHI IND TEN WAS CAR DA Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK CHI IND TEN WAS TB 52 The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? If there's a tie then they split the pot. "T. Valko" wrote: List the weekly winners across the row at the top of the sheet: ............A........B.....C...D....E 1...Winners Oak Chi Ind Ten 2...Da Boss SD CHI IND TEN 3...Al Davis OAK JAX IND TEN Then use a formula like this: =SUMPRODUCT(--(B$1:E$1=B2:E2)) Copy down as needed. As far as the tie breaker....you'd need to define *all* the rules that apply. For example: The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? -- Biff Microsoft Excel MVP "snax626" wrote in message ... I have a football pool and I'm trying to tally the correct picks. Format is as follows: Da Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK JAX IND TEN BAL TB 52 Correct picks below: OAK CHI IND TEN WAS CAR The last column is the tie-breaker team and the points that you think both teams will score. You win if the score is the same or higher i.e. combined score is 51, you predicted 44 but another entrant predicted 49. He wins because his score is higher than yours. If you can't help me with the tie-breaker that's ok. Really need an array to tally correct picks. Thanks in Advance for any assistance. db |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help setting up an array formula in Excel 2003/XP
Here's a small sample file that demonstrates this:
xPickWinners.xls 14kb http://cjoint.com/?mke5kAbJfQ I've included the tie break formula. -- Biff Microsoft Excel MVP "snax626" wrote in message ... Thanks for your quick response. I setup the SUMPRODUCT function and I'm getting an error. It's something I'm doing wrong naturally. =SUMPRODUCT(--(B$1:E$1=B2:E2)) what goes here? ^ Winners OAK CHI IND TEN WAS CAR DA Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK CHI IND TEN WAS TB 52 The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? If there's a tie then they split the pot. "T. Valko" wrote: List the weekly winners across the row at the top of the sheet: ............A........B.....C...D....E 1...Winners Oak Chi Ind Ten 2...Da Boss SD CHI IND TEN 3...Al Davis OAK JAX IND TEN Then use a formula like this: =SUMPRODUCT(--(B$1:E$1=B2:E2)) Copy down as needed. As far as the tie breaker....you'd need to define *all* the rules that apply. For example: The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? -- Biff Microsoft Excel MVP "snax626" wrote in message ... I have a football pool and I'm trying to tally the correct picks. Format is as follows: Da Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK JAX IND TEN BAL TB 52 Correct picks below: OAK CHI IND TEN WAS CAR The last column is the tie-breaker team and the points that you think both teams will score. You win if the score is the same or higher i.e. combined score is 51, you predicted 44 but another entrant predicted 49. He wins because his score is higher than yours. If you can't help me with the tie-breaker that's ok. Really need an array to tally correct picks. Thanks in Advance for any assistance. db |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help setting up an array formula in Excel 2003/XP
You are the bomb!!!
That demo that you pointed me to is just the what the doctor ordered. Thanks for your help and your timely response. The tie-breaker returns multiple winners but I can deal with that. I can't thank you enough. db "T. Valko" wrote: Here's a small sample file that demonstrates this: xPickWinners.xls 14kb http://cjoint.com/?mke5kAbJfQ I've included the tie break formula. -- Biff Microsoft Excel MVP "snax626" wrote in message ... Thanks for your quick response. I setup the SUMPRODUCT function and I'm getting an error. It's something I'm doing wrong naturally. =SUMPRODUCT(--(B$1:E$1=B2:E2)) what goes here? ^ Winners OAK CHI IND TEN WAS CAR DA Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK CHI IND TEN WAS TB 52 The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? If there's a tie then they split the pot. "T. Valko" wrote: List the weekly winners across the row at the top of the sheet: ............A........B.....C...D....E 1...Winners Oak Chi Ind Ten 2...Da Boss SD CHI IND TEN 3...Al Davis OAK JAX IND TEN Then use a formula like this: =SUMPRODUCT(--(B$1:E$1=B2:E2)) Copy down as needed. As far as the tie breaker....you'd need to define *all* the rules that apply. For example: The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? -- Biff Microsoft Excel MVP "snax626" wrote in message ... I have a football pool and I'm trying to tally the correct picks. Format is as follows: Da Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK JAX IND TEN BAL TB 52 Correct picks below: OAK CHI IND TEN WAS CAR The last column is the tie-breaker team and the points that you think both teams will score. You win if the score is the same or higher i.e. combined score is 51, you predicted 44 but another entrant predicted 49. He wins because his score is higher than yours. If you can't help me with the tie-breaker that's ok. Really need an array to tally correct picks. Thanks in Advance for any assistance. db |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help setting up an array formula in Excel 2003/XP
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "snax626" wrote in message ... You are the bomb!!! That demo that you pointed me to is just the what the doctor ordered. Thanks for your help and your timely response. The tie-breaker returns multiple winners but I can deal with that. I can't thank you enough. db "T. Valko" wrote: Here's a small sample file that demonstrates this: xPickWinners.xls 14kb http://cjoint.com/?mke5kAbJfQ I've included the tie break formula. -- Biff Microsoft Excel MVP "snax626" wrote in message ... Thanks for your quick response. I setup the SUMPRODUCT function and I'm getting an error. It's something I'm doing wrong naturally. =SUMPRODUCT(--(B$1:E$1=B2:E2)) what goes here? ^ Winners OAK CHI IND TEN WAS CAR DA Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK CHI IND TEN WAS TB 52 The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? If there's a tie then they split the pot. "T. Valko" wrote: List the weekly winners across the row at the top of the sheet: ............A........B.....C...D....E 1...Winners Oak Chi Ind Ten 2...Da Boss SD CHI IND TEN 3...Al Davis OAK JAX IND TEN Then use a formula like this: =SUMPRODUCT(--(B$1:E$1=B2:E2)) Copy down as needed. As far as the tie breaker....you'd need to define *all* the rules that apply. For example: The closest without going over If all picks are over, the closet minimum difference If there's tie with the tiebreaker, what then? -- Biff Microsoft Excel MVP "snax626" wrote in message ... I have a football pool and I'm trying to tally the correct picks. Format is as follows: Da Boss SD CHI IND TEN WAS CAR 43 Al Davis OAK JAX IND TEN BAL TB 52 Correct picks below: OAK CHI IND TEN WAS CAR The last column is the tie-breaker team and the points that you think both teams will score. You win if the score is the same or higher i.e. combined score is 51, you predicted 44 but another entrant predicted 49. He wins because his score is higher than yours. If you can't help me with the tie-breaker that's ok. Really need an array to tally correct picks. Thanks in Advance for any assistance. db |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting Field Size in Excel 2003 | Excel Discussion (Misc queries) | |||
setting up and using a database in excel 2003 | Excel Discussion (Misc queries) | |||
Setting Default Excel 2003 Toolbars | New Users to Excel | |||
Setting page length in Excel 2003 | Excel Worksheet Functions | |||
excel 2003 ... setting up "Look In " within "open" | New Users to Excel |