Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting Field Size in Excel 2003 michelle Excel Discussion (Misc queries) 2 September 9th 08 09:04 PM
setting up and using a database in excel 2003 FLYNNE Excel Discussion (Misc queries) 0 April 29th 08 02:44 PM
Setting Default Excel 2003 Toolbars CasualUser New Users to Excel 2 December 4th 07 11:03 PM
Setting page length in Excel 2003 Centurian Excel Worksheet Functions 0 January 5th 06 05:07 AM
excel 2003 ... setting up "Look In " within "open" Kev New Users to Excel 2 January 4th 05 02:08 PM


All times are GMT +1. The time now is 05:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"