negative numbers adding not subtracting

[kevhatch]

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(J2>0,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

 

[Bernie Deitrick]

Kev,

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

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

to simply

=C2+J2

That will handle all cases automatically.

HTH,
Bernie
MS Excel MVP

 

[kevhatch]

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