Hi Michael & Hans,What I understand from Michael’s issue is a limitation in how decimal values are handled when computing VAT with |m-spreadsheet|. I initially tried to solve this without Lua, using only the mechanisms provided by the module, but without success. Technically, the core point is that the format specification (e.g. |@ "0.2f €"|) controls *only the visual formatting*, not the numeric value used in subsequent calculations. Internally, values are still accumulated with full precision, which can lead to inconsistent totals.
For accounting-consistent results, rounding must therefore occur *at calculation time*, before values are reused in sums. As Hans pointed out, Lua formatting (|%.2f|) performs actual rounding and can be used reliably for this purpose.
The adopted approach is thus to separate concerns: * use *Lua* to compute line values (HT, VAT, TTC) and apply rounding, * keep *spreadsheet* for table structure and rendering.An important detail is that values coming from Lua must still be emitted using the spreadsheet syntax (|"..."| for text, |@ "0.2f €" <number>| for amounts); otherwise the table layout breaks. The attached source and PDF illustrate this workflow: VAT is rounded per line, totals are consistent, and the table structure remains intact.
It might be useful, Michael, to document this limitation and the recommended workaround on ConTeXt Garden, as the distinction between visual formatting and numeric rounding in |m-spreadsheet| is not obvious to users.
Best//JP See the source file and PDF output in the attached files. Le 10/02/2026 à 12:52, Jean-Pierre Delange via ntg-context a écrit :
Hi Michael,On my CTX version (which is ConTeXt ver: 2025.07.27 21:43 LMTX), the code I sent you compiles correctly and produces the PDF attached to this message.It's true that the problem you're encountering can be solved with Lua code, as Hans has done very well. But I tried without Lua code. And for my part, I'm testing various ways of calculating VAT rates (with or without Lua code).I'll send you the code that gives you the attached PDF. Best//JP %Michael Guravage VAT calculation \usemodule[spreadsheet] % Text for the first line of a specification \def\title{% \startrow[bottomframe=on]\startcell[align=flushleft ,width=0.48\textwidth] "{\bf \sc beschrijving}" \stopcell\startcell[align=flushright,width=0.16\textwidth] "{\bf \sc aantal}" \stopcell\startcell[align=flushright,width=0.16\textwidth] "{\bf \sc prijs}" \stopcell\startcell[align=flushright,width=0.20\textwidth] "{\bf \sc bedrag}" \stopcell\stoprow } % Simply billable \def\billable#1#2#3{% \startrow \startcell[align=flushleft ] "#1" \stopcell \startcell[align=flushright] @ "0.2f" #2 \stopcell \startcell[align=flushright] @ "0.2f €" #3 \stopcell \startcell[align=flushright] @ "0.2f €" #2 * #3 \stopcell \stoprow } % Calculate the BTW (numerically rounded) \def\btw{% \startrow[topframe=on] \startcell[align=flushleft] "BTW\ {21}\procent" \stopcell \startcell \strut \stopcell \startcell \strut \stopcell \startcell[align=flushright] @ "0.2f €" tonumber(fmt("%0.2f", sum(D) * 0.21)) \stopcell \stoprow } % Calculate the Grand Total using the rounded BTW \def\total{% \startrow[topframe=on] \startcell[align=flushleft] "{\bf Factuurbedrag}" \stopcell \startcell \strut \stopcell \startcell \strut \stopcell \startcell[align=flushright, style=bf] @ "0.2f €" sum(D) + tonumber(fmt("%0.2f", sum(D) * 0.21)) \stopcell \stoprow } \starttext \startspreadsheettable[dutch][frame=off] \title \billable{Project: Foo}{2.5}{15} \billable{Project: Bar}{2.5}{15} \billable{Project: Hat}{3.5}{15} \btw \total \stopspreadsheettable \stoptext Le 09/02/2026 à 15:59, Michael Guravage a écrit :Dear Jean-Pierre,Thanks for your quick response, Unfortunately, your mwe It doesn't compile here. The compiler stumbles over the line "\startcell[align=flushright] @ "0.2f €" tonumber(fmt("%0.2f", sum(D) * 0.21)) \stopcell" with the message, "The file ended when scanning an argument."Hans' examples work, of course, but, as you observed, the formatted representation is rounded while the underlying value is not.With kind regards, MichaelOn Mon, Feb 9, 2026 at 2:01 PM Jean-Pierre Delange via ntg-context <[email protected]> wrote:Hi Michael, It seems (AMHA) that this is not a bug but a difference between formatting and calculation. The format specifier |@ "0.2f €"|only affects how the value is /displayed/; it does not round the underlying numeric result. Internally, |sum(D) * 0.21|is still computed with full precision, and that unrounded value is then used in the total. If the VAT must be rounded according to accounting rules before it contributes to the final sum, the rounding has to be done explicitly in the expression, for example: |@ "0.2f €" round(sum(D) * 0.21, 2)| This way the VAT is numerically rounded to two decimals, and the total will come out as expected. I hope this may help, JP Le 09/02/2026 à 13:40, Michael Guravage a écrit :Hi, This MWE is a spreadsheet representing a factuur. The rows are billable items. From the sum of the rows the VAT of 21% is calculated (sourceline 32). This unrounded value results in an incorrect sum at the end. So, how can I round this value to two decimal places? With kind regards, Michael %% Start MWE \usemodule[spreadsheet] % Text for the first line of a specification \def\title{% \startrow[bottomframe=on] \startcell[align=flushleft ,width=0.48\textwidth] "{\bf \sc beschrijving}" \stopcell \startcell[align=flushright,width=0.16\textwidth] "{\bf \sc aantal}" \stopcell \startcell[align=flushright,width=0.16\textwidth] "{\bf \sc prijs}" \stopcell \startcell[align=flushright,width=0.20\textwidth] "{\bf \sc bedrag}" \stopcell %% \startcell[align=flushright,width=0.25\textwidth] "{\bf \sc btw}" \stopcell \stoprow } % Simply billable \def\billable#1#2#3{% \startrow \startcell[align=flushleft ] "#1" \stopcell \startcell[align=flushright] @ "0.2f" #2 \stopcell \startcell[align=flushright] @ "0.2f €" #3 \stopcell \startcell[align=flushright] @ "0.2f €" #2 * #3 \stopcell \stoprow } % Calculate the BTW. Now we calculate a percentage of the sum instead of the sum of the percentages. \def\btw{% \startrow[topframe=on] \startcell[align=flushleft] "BTW\ {21}\procent" \stopcell \startcell \strut \stopcell \startcell \strut \stopcell \startcell[align=flushright] @ "0.2f €" sum(D) * 0.21 \stopcell % Here's the value that needs rounding \stoprow } % Calculate the Grand Total \def\total{% \startrow[topframe=on] \startcell[align=flushleft] "{\bf Factuurbedrag}" \stopcell \startcell \strut \stopcell \startcell \strut \stopcell \startcell[align=flushright, style=bf] @ "0.2f €" sum(D) \stopcell \stoprow } \starttext % Here is the specification \startspreadsheettable[dutch][frame=off] \title % add your line items \billable{Project: Foo}{2.5}{15} \billable{Project: Bar}{2.5}{15} \billable{Project: Hat}{3.5}{15} \btw \total \stopspreadsheettable \stoptext % finis ___________________________________________________________________________________ If your question is of interest to others as well, please add an entry to the Wiki! maillist :[email protected] /https://mailman.ntg.nl/mailman3/lists/ntg-context.ntg.nl webpage :https://www.pragma-ade.nl /https://context.aanhet.net (mirror) archive :https://github.com/contextgarden/context wiki :https://wiki.contextgarden.net ______________________________________________________________________________________________________________________________________________________________________ If your question is of interest to others as well, please add an entry to the Wiki! maillist : [email protected] / https://mailman.ntg.nl/mailman3/lists/ntg-context.ntg.nl webpage : https://www.pragma-ade.nl / https://context.aanhet.net (mirror) archive : https://github.com/contextgarden/context wiki : https://wiki.contextgarden.net ___________________________________________________________________________________ ___________________________________________________________________________________ If your question is of interest to others as well, please add an entry to the Wiki! maillist :[email protected] /https://mailman.ntg.nl/mailman3/lists/ntg-context.ntg.nl webpage :https://www.pragma-ade.nl /https://context.aanhet.net (mirror) archive :https://github.com/contextgarden/context wiki :https://wiki.contextgarden.net ______________________________________________________________________________________________________________________________________________________________________ If your question is of interest to others as well, please add an entry to the Wiki! maillist :[email protected] /https://mailman.ntg.nl/mailman3/lists/ntg-context.ntg.nl webpage :https://www.pragma-ade.nl /https://context.aanhet.net (mirror) archive :https://github.com/contextgarden/context wiki :https://wiki.contextgarden.net ___________________________________________________________________________________
spreadsheet-round-vat-hans.pdf
Description: Adobe PDF document
% spreadsheet-round-vat-hans.tex
% Michael Garage VAT calaculation issue
\usemodule[spreadsheet]
% -----------------------------------------------------------------
% Lua: compute invoice numbers + output rows using spreadsheet syntax
% -----------------------------------------------------------------
\startluacode
invoice = {
items = {},
total_ht = 0.0,
total_vat = 0.0,
total_ttc = 0.0,
}
local function round2(x)
return tonumber(string.format("%.2f", x))
end
local function q(s) -- quote for spreadsheet strings: "..."
s = tostring(s or "")
s = s:gsub('"','\\"')
return '"' .. s .. '"'
end
function invoice.add(desc, qty, pu, rate)
local ht = qty * pu
local vat = round2(ht * rate) -- VAT rounded per line
local ttc = round2(ht + vat) -- TTC rounded per line
invoice.total_ht = invoice.total_ht + ht
invoice.total_vat = invoice.total_vat + vat
invoice.total_ttc = invoice.total_ttc + ttc
invoice.items[#invoice.items+1] = {
desc = desc, qty = qty, pu = pu, ht = ht, vat = vat, ttc = ttc, rate = rate
}
end
function invoice.emit_item_rows()
for _,it in ipairs(invoice.items) do
context.startrow()
context.startcell({align="flushleft"}, q(it.desc)) context.stopcell()
context.startcell({align="flushright"}, '@ "0.2f" ' .. it.qty) context.stopcell()
context.startcell({align="flushright"}, '@ "0.2f €" ' .. it.pu) context.stopcell()
context.startcell({align="flushright"}, '@ "0.2f €" ' .. it.ht) context.stopcell()
context.startcell({align="flushright"}, '@ "0.2f €" ' .. it.vat) context.stopcell()
context.startcell({align="flushright"}, '@ "0.2f €" ' .. it.ttc) context.stopcell()
context.stoprow()
end
end
function invoice.emit_totals_row()
context.startrow({topframe="on"})
context.startcell({align="flushleft"}, "{\\bf Totaux}") context.stopcell()
context.startcell() context.strut() context.stopcell()
context.startcell() context.strut() context.stopcell()
context.startcell({align="flushright", style="bf"}, '@ "0.2f €" ' .. invoice.total_ht) context.stopcell()
context.startcell({align="flushright", style="bf"}, '@ "0.2f €" ' .. invoice.total_vat) context.stopcell()
context.startcell({align="flushright", style="bf"}, '@ "0.2f €" ' .. invoice.total_ttc) context.stopcell()
context.stoprow()
end
\stopluacode
% -----------------------------------------------------------------
% TeX: header + helper to add items (Lua computes)
% -----------------------------------------------------------------
\def\InvoiceHeader{%
\startrow[bottomframe=on]
\startcell[align=flushleft ,width=0.36\textwidth] "{\bf Désignation}" \stopcell
\startcell[align=flushright,width=0.10\textwidth] "{\bf Qté}" \stopcell
\startcell[align=flushright,width=0.14\textwidth] "{\bf PU HT}" \stopcell
\startcell[align=flushright,width=0.14\textwidth] "{\bf Total HT}" \stopcell
\startcell[align=flushright,width=0.12\textwidth] "{\bf TVA 21\%}" \stopcell
\startcell[align=flushright,width=0.14\textwidth] "{\bf Total TTC}" \stopcell
\stoprow
}
\def\AddItem#1#2#3#4{%
\ctxlua{invoice.add("\luaescapestring{#1}", #2, #3, #4)}%
}
\starttext
% --- Data (example content) ---
\AddItem{Prestation conseil (janvier)}{2.50}{80.00}{0.21}
\AddItem{Déplacement}{1.00}{35.00}{0.21}
\AddItem{Licence annuelle (pro-rata)}{1.00}{119.90}{0.21}
\AddItem{Support (forfait)}{3.00}{25.00}{0.21}
% --- Output table (spreadsheet renders columns correctly) ---
\startspreadsheettable[dutch][frame=off]
\InvoiceHeader
\ctxlua{invoice.emit_item_rows()}
\ctxlua{invoice.emit_totals_row()}
\stopspreadsheettable
\stoptext
___________________________________________________________________________________ If your question is of interest to others as well, please add an entry to the Wiki! maillist : [email protected] / https://mailman.ntg.nl/mailman3/lists/ntg-context.ntg.nl webpage : https://www.pragma-ade.nl / https://context.aanhet.net (mirror) archive : https://github.com/contextgarden/context wiki : https://wiki.contextgarden.net ___________________________________________________________________________________
