Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Mac Numbers : custom data format in %

Hello,

I almost finished a template for a client who needs a Numbers document to create price offers.

I'm stuck on this last thing, I have a column for a rebate in %.

Setting the cell format as % is ok, it works, if I write 5, it shows %... but by default it shows 0% which is not nice, I would like to hide value if it equals 0.

To do that, in the inspector, I select Cell > Data Format > Custom Format. I select % and add a rule to not show anything when the value is 0.

Now, if I enter 5 in the cell to tell 5% it shows me %500 and the calculation if totally false.

I need help here to create a custom format data based on percentage, I don't know how to set it right.

User uploaded file

Posted on Mar 10, 2018 5:17 AM

Reply
Question marked as Best reply

Posted on Mar 10, 2018 2:22 PM

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.

User uploaded file

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

2 replies
Question marked as Best reply

Mar 10, 2018 2:22 PM in response to Trouille2

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.

User uploaded file

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

Mac Numbers : custom data format in %

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.