Xlsxwriter count/sum function

Question

Hi i've been using xlsxwriter to scrap some data. I've been identifying data via xpaths -> text. As shown in the code below. I've been trying to use xlsxwriter to perform some average across some cells. However the output excel file I get "#DIV/0!" in cell G2 but when I double click on each individual cell twice, that cells then gets averaged, I have to do this for all the cells I'm averaging. Any idea how I can over come this issue?

for elem in driver.find_elements_by_xpath('//*[@id="financials_table_ratio"]/tbody/tr[45]/td[2]'):
    PER1=(elem.text)
for elem in driver.find_elements_by_xpath('//*[@id="financials_table_ratio"]/tbody/tr[45]/td[3]'):
    PER2=(elem.text)
for elem in driver.find_elements_by_xpath('//*[@id="financials_table_ratio"]/tbody/tr[45]/td[4]'):
    PER3=(elem.text)
for elem in driver.find_elements_by_xpath('//*[@id="financials_table_ratio"]/tbody/tr[45]/td[5]'):
    PER4=(elem.text)

worksheet.write('B2',PER)
worksheet.write('C2',PER1)
worksheet.write('D2',PER2)
worksheet.write('E2',PER3)
worksheet.write('F2',PER4)

worksheet.write_formula('G2', '=average(C2:F2)')

score 0 · Answer 1 · answered Feb 22 '18 at 20:30

0

Excel uses uppercase AVERAGE() and not lowercase average() so you need to use that with XlsxWriter.

See the Dealing with formula errors section of the XlsxWriter docs.

answered Feb 22 '18 at 20:30

jmcnamara

38,196
6
90
108

sancho.s ReinstateMonicaCellio · Answer 2 · 2018-02-22T21:14:02.233

From Official documentation:

XlsxWriter doesn’t calculate the result of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.

This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas will only display the 0 results. Examples of such applications are Excel Viewer, PDF Converters, and some mobile device applications.

If required, it is also possible to specify the calculated result of the formula using the optional value parameter for write_formula()

So, one needs to take some action to perform the calculation, after writing the formula, if one is pressed to see the result (e.g., closing/reopoening, but it may be too "rude").

PS: Given that John McNamara gave a different answer, I guess his would be correct. From this I would guess my answer is perhaps also correct. I wouldn't know the reason for the difference 0 (in [1]) vs. #DIV/0! (in the OP).

PS2: Other interesting question: How to make unoconv auto calculate formulae before converting into pdf?

Xlsxwriter count/sum function

2 Answers2