I am trying to create a table in Calc that will have a column dealing with weights. The data that will be entered will be in lbs but I want the cell to display the weight in either lbs or tons depending on the weight entered.
So for example:
If the number entered was 205 the displayed figure would show "205 lbs."
however if the number entered was 4000 then the display would show "2 tons"
I started working with the "User Defined" format and got a start on this based on some examples I found out on the web, but not sure how to continue. At present I have...
[>=2000]#,##0.000" tons";[<2000]#,##0.000" lbs";"";""
Which will append on the "lbs." or "ton" appropriately but displays "4,000 tons" when 4000 is entered. Obviously one piece I need is to get it to divide the number by 2000 when it is >= 2000.
Can anyone please help with this? AND/OR give me a link to documentation that where I can learn/reference the syntax. It looks a little like regex but I am not a regex expert. Is it regex? If so I can just look for documentation on regex.
I am going to be doing similar columns on this spreadsheet for TIME (Hours, Days, Months, Years, etc), DISTANCE (Inches, Feet, Yards, Miles, etc).
[Solved] What is the syntax for the "User Defined" number format
-
- Posts: 3
- Joined: Sat Sep 24, 2022 12:40 pm
[Solved] What is the syntax for the "User Defined" number format
Last edited by CrimsonShroud on Sat Sep 24, 2022 7:04 pm, edited 1 time in total.
OpenOffice 4.1.6 on Windows 10
Re: What is the syntax for the "User Defined" number format
Look up "number formats;codes" in the help index.
It makes no sense to make a number look like a different number.
Has nothing to do with regex.
It makes no sense to make a number look like a different number.
Has nothing to do with regex.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: What is the syntax for the "User Defined" number format
None of the NumberFormats changes the Value itself, except an explizit %format, which divide the manual input by 100
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: What is the syntax for the "User Defined" number format
Change to metric. Then you can append a thousands separator character to the format code to go from kg to metric tonne, similarly to the percent conversion mentioned by karolus. There is no signifying character like the percent format enforces, so you need to supply unit separately in the format string, like you did in your own example.
I know: that change to metric is not going to happen any time soon.
Seriously: Any conversion of units which is not intimately tied to the number system in use, needs to happen in another cell, by way of formula. There is no such conversion by format. This is by design, and helps to prevent some accidents.
The thousands stepping I mentioned above is actually quite useful sometimes, but care needs to be taken to avoid misunderstanding.
I know: that change to metric is not going to happen any time soon.
Seriously: Any conversion of units which is not intimately tied to the number system in use, needs to happen in another cell, by way of formula. There is no such conversion by format. This is by design, and helps to prevent some accidents.
The thousands stepping I mentioned above is actually quite useful sometimes, but care needs to be taken to avoid misunderstanding.
-
- Posts: 3
- Joined: Sat Sep 24, 2022 12:40 pm
Re: What is the syntax for the "User Defined" number format
Thanx Everyone. Unfortunately the data is in pounds and the audience for the spreadsheet is only going to understand pounds and tons. The problem is some of the data is pretty large, going into the ktons. I was hoping to be able to reformat it at least into tons instead of displaying 2,000,000 lbs.
Does not look like the solution to this problem is here. I am looking into alternatives. Thanx again every one.
Does not look like the solution to this problem is here. I am looking into alternatives. Thanx again every one.
OpenOffice 4.1.6 on Windows 10
Re: What is the syntax for the "User Defined" number format
The solution I can see is to create a formula in a separate cell. If it is tabular data, the formula can be copied down a separate column. You can then choose when to display the input data and when to display the converted data, either by using column gouping or by having separate "rendering sheets" with extracted/recalculated data.CrimsonShroud wrote: ↑Sat Sep 24, 2022 7:03 pm Thanx Everyone. Unfortunately the data is in pounds and the audience for the spreadsheet is only going to understand pounds and tons. The problem is some of the data is pretty large, going into the ktons. I was hoping to be able to reformat it at least into tons instead of displaying 2,000,000 lbs.
Does not look like the solution to this problem is here. I am looking into alternatives. Thanx again every one.
How to solve this in practice depends on how your data gets into your files. Do you enter them manually, get a CSV file exported from some other entity, or what?
-
- Posts: 3
- Joined: Sat Sep 24, 2022 12:40 pm
Re: [Solved] What is the syntax for the "User Defined" number format
Currently the data is being entered manually but I am just really working on the UI and at some point in the future would want o be able to populate the data from a DB procedure. I am assuming one that would create a CSV file
OpenOffice 4.1.6 on Windows 10
Re: [Solved] What is the syntax for the "User Defined" number format
The whole "dataprocessing" should be done by exactly one unit the conversion should only happen on the user-interface ( for both input and output)
Regarding .csv: the worse thing you can do would be different units in one column on csv-export.
Regarding .csv: the worse thing you can do would be different units in one column on csv-export.
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: [Solved] What is the syntax for the "User Defined" number format
A spreadsheet has not such thing as a UI for user input. Each cell can take anything.CrimsonShroud wrote: ↑Sun Sep 25, 2022 6:47 pm Currently the data is being entered manually but I am just really working on the UI
The attached database has one table with a constraint and 2 separate forms (the UI) for user input.
The table constraint allows you to enter either lbs or tons but not both nor none.
- Attachments
-
- lbs_tons.odb
- (21.14 KiB) Downloaded 55 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice