Hi Trouille,
If you are entering the number directly into the formatted cell, the 'empty' cell should remain (and appear) 'empty' until a value is entered.
If the value in the formatted cell is generated by a formula, and the formula generates a 0 under certain circumstances, the cell will display 0%.
Example: In the table below, Column F cells are all formatted as Percentage, and decimal places set to Auto.
Column C contains a description of what is in the same row of Column F.
Column D contains what is displayed—cells displaying a number contain that number, cells that appear empty are empty.
F2: zero entered. When a value is entered directly into a cell formatted as 'Percentage' Numbers interprets that number as the numerator of a fraction whose denominator is 100. Had I entered a 5 in this cell, it would have been interpreted as 5/100, and that value (0.05) would be passed on to any formula referencing this cell.
F3: If no value has been entered into the cell, nothing is displayed. A formula referencing this cell, and requiring a numerical value, would see this as equivalent to zero.
F4, F5: Here the value in the cell is generated by a formula, in this case a simple reference to another cell.
As noted above, reference to an empty cell usually results in the referring formula returning zero (if it was expecting a number), and the zero is displayed (with a percent sign) in the cell.
But the percentage formatted cell does NOT convert the formula calculated value (as it would a directly entered value.
If the formula in F5 were a simple cell reference (=D5), the formula would return 5, and the formatting would (correctly) display that value as 500%. Where the value in the formatted cell is generated by a formula, the formula must supply the decimal value that is the same as the percentage value. Hence the need to divide the integer (5) by 100.
F^, F7: This is the same formula as in F4 and F5, with the addition of an IF statement that blocks the calculation if the source cell (in column C) is empty, and returns a null string ( "" ), a text value with zero length. As this is a text value, it is not interpreted by the percentage formatting as a formatable value, the % is not added to the display, and the cell appears 'empty.'
One caution: The arithmetic operators (+ - * / —the latter two displayed as × and ÷ in formulas) expect to find numbers and will balk at text values, including a null string, and return an error message. This may limit the formulas requiring the value in these formatted cells.
Regards,
Barry
