AVERAGEIF not computing for duplicates

[tactycHQ]

If I compute an averageif for the following series: (1,5,3,4,5), I end up with an incorrect result of 4.

I am not sure why this is happening but believe the duplicate 5 is being dropped in the calculation.

[stephenrauch]

I am going to need more information to be able to duplicate. Among other things, AVERAGEIF() requires a criteria.

[tactycHQ]

Sorry I wasn't clear. The criteria I gave is ">0". So the formula in excel is: =AVERAGEIF(C3:C7,">0"), where C3:C7 are simply 1,5,3,4,5.

I then setvalue C3 = 5 using pycel and ask for the formula to be computed

Excels' result: 4.4 pycel's result: 4.25

[stephenrauch]

This test code passes, so I am unable to duplicate

def test_average_if():
    from openpyxl import Workbook
    from pycel.excelcompiler import ExcelCompiler

    wb = Workbook()
    ws = wb.active
    ws['A1'] = 1
    ws['A2'] = 5
    ws['A3'] = 3
    ws['A4'] = 4
    ws['A5'] = 5
    ws['B1'] = '=AVERAGEIF(A1:A5,">0")'
    excel_compiler = ExcelCompiler(filename='test_unbounded_countifs', excel=wb)

    assert excel_compiler.evaluate('Sheet!B1') == 3.6

    excel_compiler.set_value('Sheet!A1', 5)
    assert excel_compiler.evaluate('Sheet!B1') == 4.4

[tactycHQ]

I see what the issue is now (I think). If I were to pass a string "5" in the set_value function, the computation results in 4.25 instead of 4.4.

That's user error on my part, so apologies for that. I assumed pycel would translate the string "5" to integer 5 automatically, but I see that's not the case.

[stephenrauch]

Pycel tries very hard to maintain the same conversion rules as Excel. Which vary depending on context.