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 ___________________________________________________________________________________
spreadsheet-rounding-formatting.pdf
Description: Adobe PDF document
___________________________________________________________________________________ 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 ___________________________________________________________________________________
