nested if problem

**jaydr** · 04-12-2009, 06:00 PM

=IF(K86=0,0,IF(K86-INT(K86)=0,(N86*9)/K86,IF(K86-INT(K86)=0.1,(N86*9)/(INT(K86)+0.3333),IF(K86-INT(K86)=0.2,(N86*9)/(INT(K86)+0.6666),0))))

1. this is a formula to calculate a pitcher's earned run avg.
2. the cell in k86 is entered in shorthand as either example .0,.1,or .2 where .0 is a whole inning, .1 is 1/3 inning or .33333, and .2 is 2/3 of an inning or .66666
3. i check to see what the remainder is to figure right calc.
4. k86 represents the pitchers innings pitched. this problem affect two the formulas that i use this if to get the right number into the calculation.
5. The formula for era is (earned runs * 9)/innings pitched my earned runs field is n86

i use the shorthand to make it look better i know i can just enter the number as 1.33333 but 1.1 looks neater.

problem

the formula works ok from .0 to 4.0 then at 4.1 or higher the results of

K86-INT(K86)

become .9999999996 which makes the era 0.0 all the time. the really weird part is if you enter example 4.0 or even 4,3 the formula works. i have looked over the formula and can't see anything wrong i have done separate calculations and if i just use the

K86-INT(K86)

in a blank cell i get the right results the right results any help in what is going on here as i never seen anything like this before where after a certain number that the math goes wrong. this happens on other formulas that rely on the inning pitched cell.

Thank you for NY HELP YOU CAN GIVE ME.

**shg** · 04-12-2009, 08:19 PM

Welcome to the forum, jaydr.

Please take a few minutes to read the forum rules about thread titles, and then amend yours accordingly.

**jaydr** · 04-13-2009, 10:17 AM

Just adding some more information and the file in the file look at any of the pitching tabs and look at the era cell look for name HENDRICKSON also look in the p/ip cell that has the same problem. i also checked if i put the cell to show 5.3333 instead of 5.1 and i get the same results of the calculation of k86-int(k86) equals .99999996 instead of .1 and it starts with 4.1. 3.1,2.1,1.1,0.1 seem to calculate the right way. this problem is frustrating in terms of how to solve it. i never seen a simple math problem go wrong after a certain point especially a subtraction problem that works with other numbers. the worksheet for the formula posted in my first post is named p vs east. here is the file so you can see if i have overlooked something.

**shg** · 04-13-2009, 10:21 AM

It's a precision error due the the way computers store numbers. Round the result to the number of significant digits you need, e.g.,

=round(yourFormula, 2)

**jaydr** · 04-13-2009, 10:30 AM

shg,

Thank you for your sugguestion. Tried it but it doesn't work. see my second post where i attached the spreadsheet try evaluating the formula with the dat in the ip cell then evaluate it changing the ip cell with 3.1 which calculates right to see what i am talking about. it is a strange problem.

**daddylonglegs** · 04-13-2009, 10:37 AM

Change the formula in the first post to the following:

=IF(K86=0,0,N86*9/(K86+MOD(K86,1)*7/3))

edit: or you can use DOLLARDE function, i.e.

=IF(K86=0,0,N86*9/DOLLARDE(K86,3))

**jaydr** · 04-13-2009, 10:57 AM

Thank you daddylong legs that worked. i still don't understand why after a certain point that math would change in my original formula especially whne the only thing that changed. and only with the figure .1 about the number 3 same subtraction that worked right before then then changes over a certain number and didn't change when you had a .0 or .2 in the number. i guess i am just trying to understand why this is happening when it should not happen and even if i entered the number as example 5.33333 it still didn't calculate right. i can get the right result if i progeny in c++ with out a problem and i just thought it would be easier in excell and i was trying to learn about nested ifs in excell also. i was going to use the mod function but looking at the help for it i thought that it wasn't what i needed i thought the int fuction would be the better answer. Again thank you for all your help

**daddylonglegs** · 04-13-2009, 11:04 AM

As shg says the error is caused by the "floating point arithmetic" that Excel uses, see here

You often get problems when your calculations include A1-INT(A1), as yours do. using MOD or DOLLARDE in this scenario should avoid the issue, although MOD sometimes causes similar problems, in which case, as shg says and also as suggested in the link, ROUND function can be used......