![]() ![]() This course is the first part of a three-part series and Specialization that focuses on the application of computing techniques in Excel/VBA to solve problems. Limitations of using excel solver function mod#It then relies upon the VBA Mod function, which doesn't have the same limitation as the MOD worksheet function."Excel/VBA for Creative Problem Solving, Part 1" is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). The function simply lets you pass two arguments to the VBA function. ' Copy function arguments to local variables Of course, you could simply create your own MOD function in VBA and use it in your formulas instead of the built-in MOD function. For modulo 2, only the right-most digit is required to determine the result anyway, so the dropped bits never affect the result, regardless of value. MOD(largenum,2^16) just drops all bits to the left of the 16th binary digit. If the numbers are viewed as binary, it's easy to see what is happening. This takes the large number modulo 2 to the 16th power, then takes the resulting value modulo 2. You could also effectively remove the MOD limit by using this formula: This checks if the number being evaluated (in this case, in cell A1) is larger than the limit, and if it is it subtracts the limit from the number before calculating the modulus. If you only need to find the modulus of a number divided by 2, then you can insert a check into your formula in the following manner: For most uses, this is limit is large enough that it will never be reached. This will solve for larger numbers much larger than the limit for MOD, but theoretically will hit the same problem when the number being evaluated reaches 134,217,728*134,217,728*divisor. There are other formulaic approaches you can use, as well. ![]() The solution suggested by Microsoft is to simply not use the MOD function and instead rely upon the following formula: ![]() Thus, the problem occurs when the number being evaluated is 268,435,456 and the divisor is 2, the number being evaluated is 402,653,184 and the divisor is 3, the number being evaluated is 536,870,912 and the divisor is 4, etc. You can find more information about the error here: īasically, the MOD function returns an error if the divisor (the second argument in the MOD function), multiplied by 134,217,728, is less than or equal to the number being evaluated (the first argument in the MOD function). Microsoft knows about this problem it seems to stem from issues with the internal formulas used by MOD. The problem is actually bigger than what Cesarettin proposes. He wonders if there is a way to use the MOD function with larger numbers and a divisor of 2? For example, if the function is MOD(268435455, 2) there is no problem. If the number is less than this, there is no problem. Cesarettin noted that the MOD worksheet function cannot produce a result when the number is being evaluated is 268,435,456 or larger and the divisor is 2. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |