formula help

[bob_niekamp]

Hello everyone. I was wondering if somebody could offer some advice as
how to best approach this dilemma.

Basically I am looking for a formula that helps us keep track of
scores when we go on golf trips. Interesting stuff, huh?

Anyways the basic idea is that we have 8 golfers. We play 72 holes.
Each golfer with the lowest score on each hole wins $5. Based on that
info, could we set something up that would pick the lowest score out
of a given range, and then attribute $5 to the person who had the
lowest score. And if the lowest score was a tie, it carried to the
next hole.

Thanks for your help!

 

[Guest]

Hello,

I just wanted to let you know that if you don't get an answer in this forum
you might want to ask your question under the "Worksheet Functions" forum of
Excel Discussion Group. They might be able to answer your question.

Regards!

 

[Guest]

Hi,

Setup a blank worksheet like this:

from cell A3 to cell A10 type the 8 golfers names
in cell B3 type this formula and copy down to row 10 and across to column S

=IF(SUMPRODUCT((B$14:B$1000>0)*($A$14:$A$1000=$A3)*(B$14:B$1000=MIN(B$14:B$1000)))>0,5,0)
this will assing $5 to each golfer with the lowest scores for all 18 holes

in cell T3 type:

=SUM(B3:S3)
and copy down to T10, this will give you the totals for each golfer

now all you need to do in input your golf scores starting in row 14 on down,
A14 being a golfers name and from B14 to S14 the first golfer's 18 hole
scores!

if you want to total you scores then in T14 type
=SUM(B14:S14)
and copy down as far as needed


Have fun!

HTH
Jean-Guy

 

[Guest]

Hi Bob

Sorry I got ahead of myself and forgot to read all the way down so didn't
see the part in advent of a tie.

put this in C3 and copy down and across as I mentioned in my last post

=IF(SUMPRODUCT((B$14:B$1000>0)*($A$14:$A$1000=$A3)*(B$14:B$1000=MIN(B$14:B$1000)))=1,5*COLUMN(B1)-SUM($B$3:B3),0)

Hope this helps!
Jean-Guy

 

[Guest]

Sorry I messed it up again...I'll keep working on it!

Jean-Guy

 

[Guest]

Hi,

Ok I think I got it.

in B3 put:

=IF(AND(SUMPRODUCT(($A$14:$A$1000=$A3)*(B$14:B$1000>0)*(B$14:B$1000=MIN(IF(B$14:B$1000>0,B$14:B$1000))))>0,SUMPRODUCT(($A$14:$A$1000=$A3)*(B$14:B$1000>0)*(B$14:B$1000=MIN(IF(B$14:B$1000>0,B$14:B$1000))))=COUNTIF(B$14:B$1000,"="&MIN(IF(B$14:B$1000>0,B$14:B$1000)))),5,0)

in C3 put:

=IF(AND(SUMPRODUCT(($A$14:$A$1000=$A3)*(C$14:C$1000>0)*(C$14:C$1000=MIN(IF(C$14:C$1000>0,C$14:C$1000))))>0,SUMPRODUCT(($A$14:$A$1000=$A3)*(C$14:C$1000>0)*(C$14:C$1000=MIN(IF(C$14:C$1000>0,C$14:C$1000))))=COUNTIF(C$14:C$1000,"="&MIN(IF(C$14:C$1000>0,C$14:C$1000)))),5*COLUMN(B1)-SUM($B$3:B$10),0)

both formulas needs to be entered using Ctrl+Shift+Enter, simply hitting the
enter key will not work, if done correctly you will see curly bracket on each
end of the formula { } in the formula bar, then oopy down and across as
before!

Hope this helps!
Jean-Guy

 

[bob_niekamp]

Hi,

Ok I think I got it.

in B3 put:

=IF(AND(SUMPRODUCT(($A$14:$A$1000=$A3)*(B$14:B$1000>0)*(B$14:B$1000=MIN(IF(­B$14:B$1000>0,B$14:B$1000))))>0,SUMPRODUCT(($A$14:$A$1000=$A3)*(B$14:B$1000­>0)*(B$14:B$1000=MIN(IF(B$14:B$1000>0,B$14:B$1000))))=COUNTIF(B$14:B$1000,"­="&MIN(IF(B$14:B$1000>0,B$14:B$1000)))),5,0)

in C3 put:

=IF(AND(SUMPRODUCT(($A$14:$A$1000=$A3)*(C$14:C$1000>0)*(C$14:C$1000=MIN(IF(­C$14:C$1000>0,C$14:C$1000))))>0,SUMPRODUCT(($A$14:$A$1000=$A3)*(C$14:C$1000­>0)*(C$14:C$1000=MIN(IF(C$14:C$1000>0,C$14:C$1000))))=COUNTIF(C$14:C$1000,"­="&MIN(IF(C$14:C$1000>0,C$14:C$1000)))),5*COLUMN(B1)-SUM($B$3:B$10),0)

both formulas needs to be entered using Ctrl+Shift+Enter, simply hitting the
enter key will not work, if done correctly you will see curly bracket on each
end of the formula { } in the formula bar, then oopy down and across as
before!

Hope this helps!
Jean-Guy

Hello everyone. I was wondering if somebody could offer some advice as
how to best approach this dilemma.

Basically I am looking for a formula that helps us keep track of
scores when we go on golf trips. Interesting stuff, huh?

Anyways the basic idea is that we have 8 golfers. We play 72 holes.
Each golfer with the lowest score on each hole wins $5. Based on that
info, could we set something up that would pick the lowest score out
of a given range, and then attribute $5 to the person who had the
lowest score. And if the lowest score was a tie, it carried to the
next hole.

Thanks for your help!- Hide quoted text -

- Show quoted text -

Wow, thanks so much for your help! I really appreciate it. Thanks
again for your persistence at making sure it was exactly right. YOU
ARE AWESOME!

 

[Guest]

You're welcome, I'm a golfer myself so was glad to help!

good luck with you skins games!

Regards!
Jean-Guy

Hi,

Ok I think I got it.

in B3 put:

=IF(AND(SUMPRODUCT(($A$14:$A$1000=$A3)*(B$14:B$1000>0)*(B$14:B$1000=MIN(IF(-B$14:B$1000>0,B$14:B$1000))))>0,SUMPRODUCT(($A$14:$A$1000=$A3)*(B$14:B$1000->0)*(B$14:B$1000=MIN(IF(B$14:B$1000>0,B$14:B$1000))))=COUNTIF(B$14:B$1000,"-="&MIN(IF(B$14:B$1000>0,B$14:B$1000)))),5,0)

in C3 put:

=IF(AND(SUMPRODUCT(($A$14:$A$1000=$A3)*(C$14:C$1000>0)*(C$14:C$1000=MIN(IF(-C$14:C$1000>0,C$14:C$1000))))>0,SUMPRODUCT(($A$14:$A$1000=$A3)*(C$14:C$1000->0)*(C$14:C$1000=MIN(IF(C$14:C$1000>0,C$14:C$1000))))=COUNTIF(C$14:C$1000,"-="&MIN(IF(C$14:C$1000>0,C$14:C$1000)))),5*COLUMN(B1)-SUM($B$3:B$10),0)

both formulas needs to be entered using Ctrl+Shift+Enter, simply hitting the
enter key will not work, if done correctly you will see curly bracket on each
end of the formula { } in the formula bar, then oopy down and across as
before!

Hope this helps!
Jean-Guy

Hello everyone. I was wondering if somebody could offer some advice as
how to best approach this dilemma.

Basically I am looking for a formula that helps us keep track of
scores when we go on golf trips. Interesting stuff, huh?

Anyways the basic idea is that we have 8 golfers. We play 72 holes.
Each golfer with the lowest score on each hole wins $5. Based on that
info, could we set something up that would pick the lowest score out
of a given range, and then attribute $5 to the person who had the
lowest score. And if the lowest score was a tie, it carried to the
next hole.

Thanks for your help!- Hide quoted text -

- Show quoted text -

Wow, thanks so much for your help! I really appreciate it. Thanks
again for your persistence at making sure it was exactly right. YOU
ARE AWESOME!