Hello
I work with tiny amounts of currency. I have a formula in a cell that calculates 5% of an amount displayed in another cell. Thus,
=A1*5 and result displays as a figure. For example, “38”
In this example the 38 equals 38cents. Is there a way to get Libreoffice Calc to display this value as:
0.38 or .38 or $0.38 in a cell?
I cant figure it out. My skills with calc are limited.
Multiplying by 5 is not the same as calculating 5%. Percent = hundredth. 5% = 5/100 = 0.05. With this, the value will be in dollars and it will be displayed as dollars, as desired.
If you really want to have the value calculated as cents in the cell, but display as dollars, you have to “cheat”. Instead of inserting a plain decimal point character in the format string, use a quoted decimal point. You can do the same with percent formats.
See attached file. The first 3 cells in column A display values as they really appear in the cell. In cells A6 and A7 I use the quoting trick and the values are as you specified. Column C shows actual values from the adjacent cells.
As demonstrated, it can be done. I do not recommend it. Making numbers appear as different from what the cell value actually holds is a recipe for mistakes. Make sure you know what you are doing!
… or even one may use =A1*5% directly, relying on Calc treating % as “*1/100” automatically.
A specific rate applied to a currency amount will often result in a value not restricted to 2 decimals.
This case shouldn’t be obscured by the format, but explicitly treated by the formula.
Example =ROUND(A1*5%;2)
To get a kind of alert if the rounding was missed, the numeric parts of the format might better be 0.00## instead of the usual 0.00.
Thanks for that effort.
(Iin addition to what was already told concerning the formual)
I would suggest you show all the currency amounts occurring in one sheet (or in a print range probably) with the same NumberFormat given by the appropiate code…
The best way to get that is, to define a dedicated CellStyle for currency deriving it from the default cell style by using the ‘Stylist’ Manage Styles (Ctrl+F11), and to apply that style to all your “currency cells”. If you want variations concerning different attributes (font size, font weight, bachground color …) you can derive specialized styles from the mentioned currency style, or in specific cases apply direct formatting to respective cells.
If you open the tab Numbers whether while formatting directly cells or while defining/editing a cell style, you find an entry Currency in the left list (below “Category”). Having selected it, the right list (below “Format”) will show predefined examples depending on your locale settings. You can choose one and get shown a Format Code describing it in the bottom field.
If the example clearly is what you want, you may ignore the code, and simply click OK.
If interested beyond that or needing something not predefined, you need to study the very specific syntax of number-format-codes.
The very valuable advantage of using cell styles is that a later change to the style will have its effect on all the cells to which that style was applied without further measures.
Thanks. I followed your direction and figured it out! Thankyou! Ive been at that for hours!