The Mod function returns #Num! error value. Here is my formula:

=MOD(H3*100,2)0.5

The above formula is used in my net value column. If the Net value is above
$3 million I get the #Num! error.

Lizz45ie wrote:
The Mod function returns #Num! error value. Here is my formula:

=MOD(H3*100,2)0.5

The above formula is used in my net value column. If the Net value is above
$3 million I get the #Num! error.

An interesting limitation/undocumented feature/bug of the MOD function.
See for explanation and work-arounds:

http://excel.tips.net/Pages/T003302_..._Function.html

I modified by formula from the suggestion: I got a Run Time error '5':
Invalid procedure call argument. "=MOD(MOD(H3,134217728*100,2))"
"smartin" wrote:

Lizz45ie wrote:
The Mod function returns #Num! error value. Here is my formula:

=MOD(H3*100,2)0.5

The above formula is used in my net value column. If the Net value is above
$3 million I get the #Num! error.

An interesting limitation/undocumented feature/bug of the MOD function.
See for explanation and work-arounds:

http://excel.tips.net/Pages/T003302_..._Function.html

If you were following this work-around:
=MOD(MOD(number,134217728*divisor),divisor)

then your formula should look like
=MOD(MOD(H3*100,134217728*2),2)


Lizz45ie wrote:
I modified by formula from the suggestion: I got a Run Time error '5':
Invalid procedure call argument. "=MOD(MOD(H3,134217728*100,2))"
"smartin" wrote:

Lizz45ie wrote:
The Mod function returns #Num! error value. Here is my formula:

=MOD(H3*100,2)0.5

The above formula is used in my net value column. If the Net value is above
$3 million I get the #Num! error.
An interesting limitation/undocumented feature/bug of the MOD function.
See for explanation and work-arounds:

http://excel.tips.net/Pages/T003302_..._Function.html

Thanks for your suggestion. It didn't work.

"smartin" wrote:

If you were following this work-around:
=MOD(MOD(number,134217728*divisor),divisor)

then your formula should look like
=MOD(MOD(H3*100,134217728*2),2)


Lizz45ie wrote:
I modified by formula from the suggestion: I got a Run Time error '5':
Invalid procedure call argument. "=MOD(MOD(H3,134217728*100,2))"
"smartin" wrote:

Lizz45ie wrote:
The Mod function returns #Num! error value. Here is my formula:

=MOD(H3*100,2)0.5

The above formula is used in my net value column. If the Net value is above
$3 million I get the #Num! error.
An interesting limitation/undocumented feature/bug of the MOD function.
See for explanation and work-arounds:

http://excel.tips.net/Pages/T003302_..._Function.html

Can you tell what you are trying to accomplish? Why do you think you
need MOD?

What isn't working? Error? Wrong result?

FWIW, as a worksheet function
=MOD(MOD(H3*100,134217728*2),2)
works for me up to a value of 999999999999.99 in H3.

If you are using VBA there was a different approach suggested on the
site below.


Lizz45ie wrote:
Thanks for your suggestion. It didn't work.

"smartin" wrote:

If you were following this work-around:
=MOD(MOD(number,134217728*divisor),divisor)

then your formula should look like
=MOD(MOD(H3*100,134217728*2),2)


Lizz45ie wrote:
I modified by formula from the suggestion: I got a Run Time error '5':
Invalid procedure call argument. "=MOD(MOD(H3,134217728*100,2))"
"smartin" wrote:

Lizz45ie wrote:
The Mod function returns #Num! error value. Here is my formula:

=MOD(H3*100,2)0.5

The above formula is used in my net value column. If the Net value is above
$3 million I get the #Num! error.
An interesting limitation/undocumented feature/bug of the MOD function.
See for explanation and work-arounds:

http://excel.tips.net/Pages/T003302_..._Function.html

I have four columns in a Excel 20007 macro that uses Visual Basic. The
columns should sum in the Net value column of the report with a value of
3,304,384. Here is the formula a I put in my macro:
"=MOD(MOD(H3*100,134217728*2),2)"

Instead of the formula summing the values I get #NUM! in the Net value.


"smartin" wrote:

Can you tell what you are trying to accomplish? Why do you think you
need MOD?

What isn't working? Error? Wrong result?

FWIW, as a worksheet function
=MOD(MOD(H3*100,134217728*2),2)
works for me up to a value of 999999999999.99 in H3.

If you are using VBA there was a different approach suggested on the
site below.


Lizz45ie wrote:
Thanks for your suggestion. It didn't work.

"smartin" wrote:

If you were following this work-around:
=MOD(MOD(number,134217728*divisor),divisor)

then your formula should look like
=MOD(MOD(H3*100,134217728*2),2)


Lizz45ie wrote:
I modified by formula from the suggestion: I got a Run Time error '5':
Invalid procedure call argument. "=MOD(MOD(H3,134217728*100,2))"
"smartin" wrote:

Lizz45ie wrote:
The Mod function returns #Num! error value. Here is my formula:

=MOD(H3*100,2)0.5

The above formula is used in my net value column. If the Net value is above
$3 million I get the #Num! error.
An interesting limitation/undocumented feature/bug of the MOD function.
See for explanation and work-arounds:

http://excel.tips.net/Pages/T003302_..._Function.html

Lizz45ie wrote:
The Mod function returns #Num! error value. Here is my formula:

=MOD(H3*100,2)0.5

The above formula is used in my net value column. If the Net value is above
$3 million I get the #Num! error.


You could eliminate the MOD() function:

=((H3-INT(H3*50)/50)*100)0.5