if function

[bronking]

take it easy (first post)
golf

if in column a (par 4) and column b (is score )
for colunm c: what/how if for to say if column b is value + 2 upto 6

so that if i hit a 4 on a par 4 c would come out 4
a 5 would come out 5
a 6 would come out 6
a 7 would come out 6
an 8 would come out 6 and so on

hopefully if i understand this i'll be able to work out par3's and 5's

any reply greatly appreciated

 

[mattjenkins]

I reckon this would do the trick:

=IF(B1<=A1+2,B1,6)

where b1 is score and a1 is par

 

[bronking]

matt your a diamond

just tried and it works

so i'll carry on with par3's and 5's

thnx ever so much

 

[David Biddulph]

take it easy (first post)
golf

if in column a (par 4) and column b (is score )
for colunm c: what/how if for to say if column b is value + 2 upto 6

so that if i hit a 4 on a par 4 c would come out 4
a 5 would come out 5
a 6 would come out 6
a 7 would come out 6
an 8 would come out 6 and so on

hopefully if i understand this i'll be able to work out par3's and 5's

any reply greatly appreciated

=MIN(B1,A1+2) or
=MIN(B1,6)
depending on what your rules are.

 

[bronking]

thnx for help

another question (related to above)

some players put in a non return on a hole (lets call it n) how do i
add this to formula so that when i input n the value will be +2 of the
par (colunm A)

ps i ve worked out first handicap yehaaaaaaa

 

[mattjenkins]

using my formula it would be this

=IF(b1="n",a1+2,if(B1<=A1+2,B1,6))

Matt

 

[daddylonglegs]

Possibly

=IF(B1="","",IF(ISNUMBER(B1),MIN(B1,A1+2),A1+2))

 

[bronking]

just tried (brilliant )

thanx again matt

cheers david and daddy for input

 

[bronking]

last q (promise, for today)

if "n" is inputed how do i make the column come up with number (2 mor
than par)

for instance
par 4 (score n) net 6

thnx to matt it does the formula to get 6, but total gross won'
properly add up
as in

par I grs I net
4 I 6 I 6
4 I 7 I 6
4 I n I 6

tot I ? I 18

or better still leave it showing n but twiddle with auto sum to sa
when n use net scor

 

[daddylonglegs]

If the range with the "n"s is B1:B10 then use this formula to su
assuming n=6

=SUM(B1:B10)+COUNTIF(B1:B10,"n")*6

note if you want the formula to automatically sum n as 2 more than pa
then try

=SUM(B1:B10)+COUNTIF(B1:B10,"n")*(A1+2

 

[daddylonglegs]

Edit to last post:

typo in formula, should be

=SUM(B1:B10)+COUNTIF(B1:B10,"n")*6

 

[bronking]

to daddylonglegs (or anyone who might know)

=SUM(B1:B10)+COUNTIF(B1:B10,"n")*(A1+2)

dosn't work problem with countif part of formulae

anybody any ideas ?

 

[daddylonglegs]

Assuming A1 contains par, e.g. 4 then

=SUM(B1:B10)+COUNTIF(B1:B10,"n")*(A1+2)

works OK when I test it

What result do you get? What do you mean there is a problem with
COUNTIF?

 

[bronking]

hey up m8

see piccy


+-------------------------------------------------------------------+
|Filename: count.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4980 |
+-------------------------------------------------------------------+

 

[daddylonglegs]

Looks like you're using Microsoft Works which isn't the same as
Microsoft Excel. Have you got Excel? COUNTIF will work there...

 

[bronking]

do you know i was just wondering if that was the prob (honest:confused
)

yes your right i'm using microsoft works

i assumed (wrongly) that they were compatable/sameish

thanx m8 i'll sort a copy of exce

 

[bronking]

right now with excel (borrowed daughters computor)

the formula that dadylonglegs gave does work but...

whenever "n" is inputed it only it only counts it as one value (ie 2
over par (hole1)
i need it to count it as 2 over par on which ever hole it is
(par3's,4's and 5's)

hope ive explained myself properly

any ideas

 

[daddylonglegs]

Based on your previous screenshot try this formula in C21

=SUM(C3:C20)+SUMPRODUCT(--(C3:C20="n"),B3:B20+2

 

[daddylonglegs]

.....or an array formula which need to be confirmed with
CTRL+SHIFT+ENTER

=SUM(IF(C3:C20="n",B3:B20+2,C3:C20))