Nested Formula Giving Incorrect Answer
 

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.

Nested Formula Giving Incorrect Answer
 

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?
--
David Biddulph

"William McCann" wrote in message
...
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.

Nested Formula Giving Incorrect Answer
 

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:

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.

Nested Formula Giving Incorrect Answer
 

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.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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?
--
David Biddulph

"William McCann" wrote in
message ...
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.

Nested Formula Giving Incorrect Answer
 

On Tue, 1 Dec 2009 04:37:01 -0800, William McCann
wrote:

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