Fresh Excel Tips

This tip explains how to convert an Arabic numeral to a Roman numeral. To get this result, use the ROMAN function, which returns the Roman value as text. Here is the syntax that is used: ROMAN(number, form) number: The Arabic numeral to be converted. form: (optional) A number from 0 to 4 that specifies the type of Roman [...]

In this example, we want to determine whether a column’s index in a range is even or odd, and then fill each odd row with the character “X”. Use the ISODD function in combination with IF and COLUMN() to get the result shown in the image below. If this function is not available and returns [...]

In this example, we need to determine whether a row number in a range is even or odd, and then fill each odd row with the character “X”. Use the ISODD function in combination with IF and ROW() to get the result shown in the image below. If this function is not available and returns [...]

To find out whether numbers are even or odd, use the ISEVEN or ISODD functions. ISEVEN returns TRUE if the number is even and FALSE if the number is odd, while ISODD returns TRUE if the number is odd and FALSE if the number is even. ISEVEN(number) ISODD(number) number: The value to be tested. Non-integer values are truncated. To [...]

In our previous articles, we went over standard functions for rounding up a number. In addition to those, there are other functions available like EVEN and ODD. For example, to round up a number to the nearest even integer, use the EVEN function. Here is the syntax that is used for both the even and [...]

To generate randomized values Excel provides the RAND function. This function returns a random number greater than or equal to 0 and less than 1. Each time the worksheet is calculated, a new random number is generated. This example generates randomized integer values from 1 to 999 in cells A2:D10 and then replaces the formulas [...]

Sometimes it is necessary to mark every other row in a worksheet. Several functions can be used in combination to do this. Use the MOD, ROW, and IF functions together as described below. To mark every other row: Select cells A1:A10 and type the following formula:=IF(MOD(ROW(),2),”XXX”,” “). Press Ctrl+Enter. Please note that If every other column has to be [...]

In this example, values are listed in columns A and B. Excel should calculate the product of each value in a row but only if both values exist. If one value is missing, the result is an empty cell, as shown in column C. To get the desired results, use the PRODUCT function in combination [...]

As seen in our previous article, it is easy to round up values to multiples of 100. To round numbers down to the nearest multiple of significance, use the FLOOR function. Here is the syntax that is used: FLOOR(number, significance) number: The value to be rounded. significance: The multiple to which the number will be rounded down. To round [...]

For this example, all prices have to be rounded up to whole $100 units. To do this, you use the CEILING function. This function returns a number that is rounded up to the nearest multiple of significance. CEILING(number, significance) number: The value to be rounded. significance: The multiple to which the number will be rounded up. To round up [...]