By wrapping number inside the ABS function, we eliminate this difference. This formula will work fine with positive or negative inches as input. The final result carries the sign of the original inches value. The MOD function also behaves differently with negative numbers. TRUNC, on the other hand, just chops off the decimal value and keeps the integer with the sign. The length in centimeters is equal to the length in inches multiplied by 2.54. Since one inch is equal to 2.54 centimeters, the formula used to convert inches to centimeters is: centimeters inches × 2.54. This is necessary because the INT function, contrary to its name, actually rounds negative numbers down away from zero. To convert a measurement in inches to one in centimeters, you can use a simple formula. Here, we replace INT with the TRUNC function and use the ABS function to make sure that number inside MOD is positive. To work with negative inches as an input, we need to adjust the original formula as follows: =TRUNC(B5/12)&"' "&MOD(ABS(B5),12)&"""" To output a value like "8 feet 4 inches", you adapt the formula like this: =INT(B5/12)&" feet "&MOD(B5,12)&" inches" To round inches to a given number of decimal places, wrap the MOD function in ROUND. ![]() The outer pair indicates text, and inner pair is needed for Excel to output a single double quote.įinally, the INT code and MOD code are concatenated together and Excel returns the final text value. ![]() The result is concatenated to two sets of double quotes. Configured in this way, MOD returns the remainder after division. Where number comes from B5 and the divisor is 12. To get a value for inches, the MOD function is used like this: MOD(B5,12)&"""" The result is then concatenated to a string with a single quote and space character. ![]() Inside INT, the value in B5 is divided by 12 and INT simply returns the integer portion of the result, discarding any decimal remainder. To get the value for feet, the INT function is used like this: =INT(B5/12)&"' " This formula converts a numeric value in inches to text representing the same measurement in inches and feet.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |