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]

"bronking" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
--
David Biddulph

[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 more than par)

for instance
par 4 (score n) net 6

thnx to matt it does the formula to get 6, but total gross won't 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 say when n use net score

[daddylonglegs]

If the range with the "n"s is B1:B10 then use this formula to sum 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 par 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

[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 )

yes your right i'm using microsoft works

i assumed (wrongly) that they were compatable/sameish

thanx m8 i'll sort a copy of excel

[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))