TableTotalSettings Formulas transformed by Excel

[PhidarkMM]

Hi,

First of all thank you for this nice module.

It seems that Table Totals are transformed by Excel, adding a @ to some formulas.

$data =@" Bank,Units,Cost A1,100,5 A2,120,10 A1,140,3 A3,160,20 A2,120,20 A4,140,5 "@ | ConvertFrom-Csv

$TotalSettings += @{"Bank" = @{Function = "=SOMME(SI(FREQUENCE(SI(NBCAR(A2:A7)>0;EQUIV(A2:A7;A2:A7;0);"");SI(NBCAR(A2:A7)>0;EQUIV(A2:A7;A2:A7;0);""))>0;1))";Comment = "Sum of unique Banks"}} $TotalSettings += @{"Units" = @{Function = "=SOMME(SI(FREQUENCE(SI(NBCAR(B2:B7)>0;EQUIV(B2:B7;B2:B7;0);"");SI(NBCAR(B2:B7)>0;EQUIV(B2:B7;B2:B7;0);""))>0;1))";Comment = "Sum of unique Units"}} $TotalSettings += @{"Cost" = @{Function = "=SOMME(C2:C7)";Comment = "Costs Sum"}}

$ExcelParams = @{ Path = "c:\temp\bug.xlsx" Show = $false Verbose = $false FreezeTopRow = $true BoldTopRow = $true AutoSize = $true AutoFilter = $true TableStyle = "Dark2" WorksheetName = "Bug" TableTotalSettings = $TotalSettings }

$data | Export-Excel @ExcelParams -KillExcel

Result: =@SOMME(SI(@FREQUENCE(SI(@NBCAR(A2:A7)>0;EQUIV(A2:A7;A2:A7;0);"");SI(@NBCAR(A2:A7)>0;EQUIV(A2:A7;A2:A7;0);""))>0;1)) =@SOMME(SI(@FREQUENCE(SI(@NBCAR(B2:B7)>0;EQUIV(B2:B7;B2:B7;0);"");SI(@NBCAR(B2:B7)>0;EQUIV(B2:B7;B2:B7;0);""))>0;1)) =@SOMME(C2:C7)

After manual edit without @:

Is there a way to make excel stop transforming calculated Items ?

Microsoft talking about Implicit intersection

https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34

Some article talking about .Formula2

https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2#translating-from-rangeformula2-to-rangeformula

Thanks

[TWalijew]

I ran into a similar issue and haven't found a solution so far. In the sample code below the resulting formulae: C1: "=MITTELWERT(WENN(ISTZAHL(@TEst!OSDuration); TEst!OSDuration))" C2: "=MITTELWERT(WENN(ISTZAHL(@TEst!OSDuration); TEst!OSDuration))" Workaound in C3: =MITTELWERTWENN(TEst!OSDuration;"<>#NV") Nvertheless I haven' found a way to get rid of the extraneous '@'. My sample code looks like:

remove-item .\test.xlsx -Force -ea SilentlyContinue
$xl= Open-ExcelPackage .\test.xlsx -create
$header="OSDuration"
$leftCsv =@"
    OSDuration
      7,05
     10,10
      7,90
      5,74
      =NA()
      8,72
      6,43
      7,07
      6,61
      8,70
"@| ConvertFrom-Csv
    $xl=$leftCsv|Export-Excel -ExcelPackage $xl -clearsheet -WorksheetName "TEst" -TableStyle medium12 -AutoNameRange -PassThru
    $ws=$xl.Workbook.Worksheets["Test"]
    $ws.Cells["c1"].Formula="average(IF(ISNUMBER(Test!OSDuration), Test!OSDuration))"
    $ws.Cells["c2"].Formula="=average(IF(ISNUMBER(Test!OSDuration), Test!OSDuration))"
    $ws.Cells["c3"].Formula="averageif(Test!OSDuration,`"<>#NV`")"

    Close-ExcelPackage $xl

[TWalijew]

After a long-lasting Google search I could find a solution on https://github.com/EPPlusSoftware/EPPlus/issues/591 fixing the doubtful formula with '_xlfn.'. I wonder whether is a general one.

$ws.Cells["c1"].Formula="=_xlfn.minifs(Test!OSDuration,Test!OSDuration,`"<>#NV`")"
$ws.Cells["c2"].Formula="=_xlfn.maxifs(Test!OSDuration,Test!OSDuration,`"<>#NV`")"
$ws.Cells["c3"].Formula="=_xlfn.averageif(Test!OSDuration,`"<>#NV`")"

[PhidarkMM]

Hello @TWalijew,

$TotalSettings += @{"Bank" = @{Function = "=_xlfn.SUM(SI(_xlfn.FREQUENCY(SI(LEN(A2:A7)>0;EQUIV(A2:A7;A2:A7;0);"");SI(LEN(A2:A7)>0;EQUIV(A2:A7;A2:A7;0);""))>0;1))";Comment = "Sum of unique Banks"}} -> becames =SOMME(SI(FREQUENCE(SI(NBCAR(@A2:A7)>0;EQUIV(A2:A7;A2:A7;0);"");SI(NBCAR(@A2:A7)>0;EQUIV(A2:A7;A2:A7;0);""))>0;1)) Some @ chars remains in ranges but i don't really know how to make them disappear.

$TotalSettings += @{"Cost" = @{Function = "=_xlfn.SUM(C2:C7)";Comment = "Costs Sum"}} -> becames OK in Excel =SOMME(C2:C7)

[trackd]

here is a working example,

$Subtotals = @{
    Age      = @{
        Function = '=SUBTOTAL(109;[Age])'
        Comment  = 'Sum Age'
    }
    StreetNumber = @{
        Function = '=SUBTOTAL(108;[StreetNumber])'
        Comment  = 'Avg Streetnumber'
    }
}
$array = @'
Name, Age, City, StreetNumber
John, 23, New York, 123
Jane, 25, Los Angeles, 456
Steve, 30, Chicago, 789
Fiona, 35, Miami, 101
'@ | ConvertFrom-Csv

$array | Export-Excel -TableTotalSettings $Subtotals -Show

[PhidarkMM]

Hello @trackd,

Thanks a lot, it works for me:

$data =@" Bank,Units,Cost A1,100,5 A2,120,10 A1,140,3 A3,160,20 A2,120,20 A4,140,5 "@ | ConvertFrom-Csv

$TotalSettings += @{"Bank" = @{Function = "=SUMPRODUCT(1/COUNTIF(A2:A7;A2:A7))";Comment = "Sum of unique Banks"}} $TotalSettings += @{"Units" = @{Function = "=SUMPRODUCT(1/COUNTIF(B2:B7;B2:B7))";Comment = "Sum of unique Units"}} $TotalSettings += @{"Cost" = @{Function = "=SUBTOTAL(109;C2:C7)";Comment = "Costs Sum"}}

$ExcelParams = @{ Path = "c:\temp\bug.xlsx" Show = $false Verbose = $false FreezeTopRow = $true BoldTopRow = $true AutoSize = $true AutoFilter = $true TableStyle = "Dark2" WorksheetName = "Bug" TableTotalSettings = $TotalSettings }

$data | Export-Excel @ExcelParams -KillExcel