[GH-ISSUE #351] doubt with different formula totals #280

New issue

Closed

opened 2026-05-05 12:05:50 -06:00 by gitea-mirror · 1 comment

gitea-mirror commented

2026-05-05 12:05:50 -06:00

Owner

Originally created by @sapomuyverde on GitHub (Jul 31, 2021).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/351

Originally assigned to: @jmcnamara on GitHub.

I am using this way to write a formula

numberPercentageRight= workbook_add_format(workbook)
format_set_align(numberPercentageRight, LXW_ALIGN_RIGHT)
format_set_align(numberPercentageRight, LXW_ALIGN_VERTICAL_CENTER)
format_set_font_size(numberPercentageRight, 09)
format_set_font_name(numberPercentageRight, "Calibri")
format_set_bold(numberPercentageRight)  
format_set_num_format(numberPercentageRight, "#.0%;[Red]-#.0%;-")  

worksheet_write_formula(worksheet, 14,  7, "=( (2/100) * (E21/J10) )", numberPercentageRight)

then =SUMA(I86:N86)*H15 the range I86:N86 is obtained dynamically

and I get a difference, I mean Excel displays a total for 4,787.55 and the check in the calculator returns me 4,666.51

is there something i'm doing wrong, any suggestions to try another way?.

Originally created by @sapomuyverde on GitHub (Jul 31, 2021). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/351 Originally assigned to: @jmcnamara on GitHub. I am using this way to write a formula ``` numberPercentageRight= workbook_add_format(workbook) format_set_align(numberPercentageRight, LXW_ALIGN_RIGHT) format_set_align(numberPercentageRight, LXW_ALIGN_VERTICAL_CENTER) format_set_font_size(numberPercentageRight, 09) format_set_font_name(numberPercentageRight, "Calibri") format_set_bold(numberPercentageRight) format_set_num_format(numberPercentageRight, "#.0%;[Red]-#.0%;-") worksheet_write_formula(worksheet, 14, 7, "=( (2/100) * (E21/J10) )", numberPercentageRight) ``` ![img1](https://user-images.githubusercontent.com/15135396/127748308-56c8a51f-782d-4b95-92f9-93cb1292d3b3.png) then =SUMA(I86:N86)*H15 the range I86:N86 is obtained dynamically ![img2](https://user-images.githubusercontent.com/15135396/127748328-e9a3f5e9-9657-40c3-b600-da52bf443c53.png) and I get a difference, I mean Excel displays a total for 4,787.55 and the check in the calculator returns me 4,666.51 is there something i'm doing wrong, any suggestions to try another way?.

gitea-mirror closed this issue

2026-05-05 12:05:51 -06:00

gitea-mirror commented

2026-05-05 12:05:55 -06:00

Author

Owner

@jmcnamara commented on GitHub (Aug 1, 2021):

Libxlsxwriter doesn't calculate the result of formulas. Excel does that when it loads the file.

Also, the calculation looks correct:

$ python
Python 2.7.17 (default, Dec 23 2019, 21:25:34)

>>> 2.0/100.0 * 361885.06/414983.00
0.017440958304316082

>>> (2.0/100.0 * 361885.06/414983.00) * 274500.15
4787.545670678511

Either way this isn't a libxlsxwriter issue. Closing.

@jmcnamara commented on GitHub (Aug 1, 2021): Libxlsxwriter doesn't calculate the result of formulas. Excel does that when it loads the file. Also, the calculation looks correct: ```python $ python Python 2.7.17 (default, Dec 23 2019, 21:25:34) >>> 2.0/100.0 * 361885.06/414983.00 0.017440958304316082 >>> (2.0/100.0 * 361885.06/414983.00) * 274500.15 4787.545670678511 ``` Either way this isn't a libxlsxwriter issue. Closing.