Nested Formula Giving Incorrect Answer

[William McCann]

Hi,

I am looking at an issue one of our staff has. He is using the below formula
across a set of data

=IF(INT(MOD(E13,C14))>1,INT(E13/C14),0)

This returns 0, if E13 is not disvisible by C14, or the closest integer
(rounded down) if it is. The formula works perfectly for most of the data,
but when E13 is 50,000 it throws a wobbler and returns 0, it doesn't matter
what the value of C14 is. After doing a bit of testing, and typing figures
into the formula manually, I have figured out that if a number between 50,000
and 50,002 is entered, the formula returns 0, above or below this range it is
OK.

The user has MS Office 2003, SP3, we have also tested this on Office 2007,
SP1 and we are geting the same result.

I haven't a clue why this is happening, na dwhy between that range of numbers.

Anyone got any ideas?

Thanks in advance,

Will.

 

[David Biddulph]

Doesn't behave that way for me (with Excel 2003 SP3). I have 49 in C14,
50001 in E13, and it returns 1020.

What values are you using?

 

[Stefi]

The formula which does what you described is
=IF(MOD(E13,C14)=0,INT(E13/C14),0)

INT function doesn't round but truncate!

Regards,
Stefi

„William McCann†ezt írta:

 

[David Biddulph]

I note also that your description is very strange.

You said:
"This returns 0, if E13 is not disvisible by C14, or the closest integer
(rounded down) if it is."
but your formula doesn't seem to be trying to do anything like that.

 

[Ron Rosenfeld]

Hi,

I am looking at an issue one of our staff has. He is using the below formula
across a set of data

=IF(INT(MOD(E13,C14))>1,INT(E13/C14),0)

This returns 0, if E13 is not disvisible by C14, or the closest integer
(rounded down) if it is. The formula works perfectly for most of the data,
but when E13 is 50,000 it throws a wobbler and returns 0, it doesn't matter
what the value of C14 is. After doing a bit of testing, and typing figures
into the formula manually, I have figured out that if a number between 50,000
and 50,002 is entered, the formula returns 0, above or below this range it is
OK.

The user has MS Office 2003, SP3, we have also tested this on Office 2007,
SP1 and we are geting the same result.

I haven't a clue why this is happening, na dwhy between that range of numbers.

Anyone got any ideas?

Thanks in advance,

Will.

How about giving some real numbers for both cells, what you expect, and what is
being returned.

For example, on my system (2007 SP2), it is NOT the case that for values
50000-50002 that the contents of C14 is irrelevant.

For example, E13 50,001 C14 140 Your formula --> 357
--ron