negative numbers adding not subtracting
 

Hi, probably me doing something wrong!! but .. hope someone can figure this
out ....
I am trying to get Excel (2003) to work out a golf handicap for me based on
daily rounds.. I have it all working apart from one part. Where I have a
minus figure Excel adds it rather than subtracting it.
C2 has a manually inputted number, Handicap of 1-28.
E2 totals the Net Score from the Front 9 and Back 9 sheets.
H2 is the difference between E2 and the Par of the course ( 70 - entered in
cell A1 on the Front 9 sheet ).
I2 is the stroke adjustment =H2/5 .
J2 Rounds up/down =ROUND(I2,0) .
K2 should return the modified Handicap
=IF(J2=0,C2,IF(J20,C2+J2,IF(J2<0,C2-J2))). but doesn't recognise a minus
figure in J2 so it adds it ...

i.e. A Handicap of 28(C2) shoots a net round of 65(E2) Stroke difference of
-5(H2) which is -1 stroke off the Handicap(J2) which should result in K2
reading 27 ... it totals 29 for me!!!!!!!!!!!

Any help??

Thanks in advance
Kev

negative numbers adding not subtracting
 

Kev,

Subtracting a negative number is the same as adding the positive of the number. Change your formula
from

=IF(J2=0,C2,IF(J20,C2+J2,IF(J2<0,C2-J2)

to simply

=C2+J2

That will handle all cases automatically.

HTH,
Bernie
MS Excel MVP


"kevhatch" wrote in message
...
Hi, probably me doing something wrong!! but .. hope someone can figure this
out ....
I am trying to get Excel (2003) to work out a golf handicap for me based on
daily rounds.. I have it all working apart from one part. Where I have a
minus figure Excel adds it rather than subtracting it.
C2 has a manually inputted number, Handicap of 1-28.
E2 totals the Net Score from the Front 9 and Back 9 sheets.
H2 is the difference between E2 and the Par of the course ( 70 - entered in
cell A1 on the Front 9 sheet ).
I2 is the stroke adjustment =H2/5 .
J2 Rounds up/down =ROUND(I2,0) .
K2 should return the modified Handicap
=IF(J2=0,C2,IF(J20,C2+J2,IF(J2<0,C2-J2))). but doesn't recognise a minus
figure in J2 so it adds it ...

i.e. A Handicap of 28(C2) shoots a net round of 65(E2) Stroke difference of
-5(H2) which is -1 stroke off the Handicap(J2) which should result in K2
reading 27 ... it totals 29 for me!!!!!!!!!!!

Any help??

Thanks in advance
Kev

negative numbers adding not subtracting
 

Thanks Bernie, worked a treat, and so simple! Pays not to overcomplicate
things I guess.
Kev

"Bernie Deitrick" wrote:

Kev,

Subtracting a negative number is the same as adding the positive of the number. Change your formula
from

=IF(J2=0,C2,IF(J20,C2+J2,IF(J2<0,C2-J2)

to simply

=C2+J2

That will handle all cases automatically.

HTH,
Bernie
MS Excel MVP


"kevhatch" wrote in message
...
Hi, probably me doing something wrong!! but .. hope someone can figure this
out ....
I am trying to get Excel (2003) to work out a golf handicap for me based on
daily rounds.. I have it all working apart from one part. Where I have a
minus figure Excel adds it rather than subtracting it.
C2 has a manually inputted number, Handicap of 1-28.
E2 totals the Net Score from the Front 9 and Back 9 sheets.
H2 is the difference between E2 and the Par of the course ( 70 - entered in
cell A1 on the Front 9 sheet ).
I2 is the stroke adjustment =H2/5 .
J2 Rounds up/down =ROUND(I2,0) .
K2 should return the modified Handicap
=IF(J2=0,C2,IF(J20,C2+J2,IF(J2<0,C2-J2))). but doesn't recognise a minus
figure in J2 so it adds it ...

i.e. A Handicap of 28(C2) shoots a net round of 65(E2) Stroke difference of
-5(H2) which is -1 stroke off the Handicap(J2) which should result in K2
reading 27 ... it totals 29 for me!!!!!!!!!!!

Any help??

Thanks in advance
Kev