Working with Truncated Numbers

[flywire]

Any tips on working with truncated numbers in Calc?

I understand the normal practice is to enter original numbers in a cell and display =trunc(cell) beside it.

I’d like to entering original decimal data and truncate in-place (formatting would be ideal). I also need to sum the truncated numbers which aren’t in continuous ranges.

I’d use a macro if necessary.

Sample data:

Item A  1.1
Item B  2.6
Total   =formula()

Required:

Item A  1
Item B  2
Total   3

---

Edit: The actual document is a little more complicated as it contains amounts that are truncated, amounts that are rounded to two decimal places, and negative numbers. It’s not reasonable to just truncate all numbers.

[JohnSUN]

@flywire

Total =formula()

=INT(A2)+INT(B2)?

[flywire]

I’ll flick this to AI:

INT Vs TRUNC:
The crucial distinction lies in their behaviour with negative numbers. TRUNC removes the decimal part, moving the number closer to zero (e.g., -4.7 becomes -4). INT, on the other hand, rounds down to the nearest integer, which for negative numbers means moving further away from zero (e.g., -4.7 becomes -5).

After further investigation I understand formatting always rounds, so essentially original data needs to be transformed to display it truncated. That surprised me.
.
Currently searching for opportunity to (ab)use Tools, Options, LibreOffice Calc, Calculate, Precision as shown.

[robleyd]

@flywire

flick this to AI

The same information is of course available in the Help pages for those functions. Not exactly the same wording however.

[JohnSUN]

@flywire

essentially original data needs to be transformed

I’m trying to understand the problem you’re solving. May be it really be easier to use a macro to read all the data and return the trimmed values ​​to the same locations? Or would it be better to create a copy of the trimmed, “cleaned” data on a separate sheet? And by the way, will your data contain negative numbers?

[flywire]

Problem is a simplified Trust-tax-return-2024.pdf in a spreadsheet which is a bloody mess, and it includes negative numbers. Data is sourced from elsewhere.
.
Eventually looking to generate example-fields-filled-capture.csv with correct calculations based on truncated numbers.
.
Using the form for input to say a database could solve the issue. I’m still prototyping.

[JohnSUN]

If I were faced with this problem, I’d try starting with something like this:

Sub copyTrunc()
Dim oSheets As Variant, oCurrentController As Variant,  oActiveSheet As Variant
Dim sName As String, sCell As String
Dim oCursor As Variant, aFormulaArray As Variant, aTemp  As Variant
Dim iRow As Long, iCol As Long
	oSheets = ThisComponent.getSheets()
	oCurrentController = ThisComponent.getCurrentController()
	oActiveSheet = oCurrentController.getActiveSheet()
	sName = oActiveSheet.getName() & "_trunc"
	If oSheets.hasByName(sName) Then oSheets.removeByName(sName)
	oSheets.insertNewByName(sName, oSheets.getCount())

	oCursor = oActiveSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	aFormulaArray = oCursor.getFormulaArray()
		For iRow = LBound(aFormulaArray) To UBound(aFormulaArray)
			For iCol = LBound(aFormulaArray(0)) To UBound(aFormulaArray(0))
				sCell =aFormulaArray(iRow)(iCol)
				If Left(Trim(sCell),1) <> "=" Then
					aTemp = Split(sCell, ".")
					If (UBound(aTemp) > 0) Then aFormulaArray(iRow)(iCol) = aTemp(0)
				EndIf
			Next iCol
		Next iRow
	oSheets.getByName(sName).getCellRangeByPosition(0,0,UBound(aFormulaArray(0)),UBound(aFormulaArray)).setFormulaArray(aFormulaArray)
End Sub

and then improve the code as I discover bugs.

[mariosv]

You can divide the value represented by a thousand with each thousand separator you add at the end of the number format. ‘.’ or ‘,’ depends on the cell language.

[sokol92]

@flywire

Currently searching for opportunity to (ab)use Tools, Options, LibreOffice Calc, Calculate, Precision as shown.

Perhaps this is what you need.
This setting applies to and is saved at the document level.
If a cell is formatted as an integer, then:

1. When entering a fractional number in the cell, the rounded integer is displayed.
2. If a formula contains a reference to this cell, the rounded displayed value (integer) is substituted into the formula, not the cell value (fractional).

As far as I understand (I am a certified professional accountant ), this approach can be used when completing financial documents.

[keme1]

Instead of TRUNC() or INT(), use ROUND() which “rounds to nearest”. With that, you can also specify how many decimals to keep. Specifying “negative count” of decimals to the function means that you round to tens, hundreds, thousands, etc.

Typically, in tax documents I have worked with (currency NOK exclusively), individual numbers are rounded to nearest ten, and end result rounds down by the 100. For other currencies and other nationalities, different rounding rules may be in force.

You can also use MROUND() if you need to round by other than tens. (Nearest even number, fifties, etc.)
CEILING() and FLOOR() has the same “granular capability” as MROUND() but always round up/down, respectively.

Using “precision as shown” is a dangerous practice. It is fine as a tool for a quick check of “how is it going to be” but it does round everything, which represents a real risk of grave miscalculations, particularly when small numbers are involved. If you have intermediate calculations which do not represent a “transaction level”, make sure you know what you are doing!

@sokol92

As far as I understand (I am a certified professional accountant ), this approach can be used when completing financial documents.

As a professional, you may be right with respect to the accounting part. However, the distinction between “transaction level” figures (which should add up “to the penny”; precision as shown makes sound sense) and intermediate calculations with smaller fractions (involving percentages and divisions, also significant in some financial contexts) should not be taken lightly.

[flywire]

@keme1

Instead of TRUNC() or INT(), use ROUND() which “rounds to nearest”.

There is a lot of insight in this post but that is just wrong, TRUNC() is the only correct way.
.
Precision is a side issue that also needs considering (ie 1.999999999999999 is really 2 not 1). I’m currently thinking it is probably more reasonable to round to two decimal places, then truncating where required.

[keme1]

@flywire

[…] TRUNC() is the only correct way.
.
Precision is a side issue that also needs considering (ie 1.999999999999999 is really 2 not 1). I’m currently thinking it is probably more reasonable to round to two decimal places, […]

Yes. If you really must use TRUNC() (I recognize that there are use cases which I do not see) that rounding step would most likely be the intermediate calculation you need.

You may also consider round to 3 decimals. That would make even TRUNC(x;2) (two decimals, i.e. to the penny") dismiss the rounding but ROUND() will still eliminate the rounding error introduced by binary/decimal “incompatibility”.