Execute Excel formula using powershell-CodePudding

I have the below data in an excel file

SYMM_ID     DATE                INSTANCE                    Total Response Time
297900076   01-06-2022 05:00    SG_SG_ORACLUL_L_PRDPRF      0.31
297900076   01-06-2022 05:05    SG_SG_ORACLUL_L_PRDPRF      0.5
297900076   01-06-2022 14:50    SG_SG_ORACLUL_L_PRDPRF      0.62
297900076   01-06-2022 14:55    SG_SG_ORACLUL_L_PRDPRF      0.53
297900076   01-06-2022 15:00    SG_SG_ORACLUL_L_PRDPRF      0.61
297900076   01-06-2022 15:05    SG_SG_ORACLUL_L_PRDPRF      0.7

Manually in a different sheet, i am executing the below excel formula to do some calculation.

=AVERAGEIFS(SLA!$D:$D,SLA!$C:$C,"SG_SG_*_L_*",SLA!$D:$D,"<>0")

I need to automate this process.

Please, I need some references on how to execute this formula and put data in same/different sheet

Below I was trying, but I am not getting any luck

$excel = new-object -comobject Excel.Application
$excel.visible = $false  
$workbook = $excel.workbooks.open("C:\SFile.xlsx")
 
$worksheet = $workbook.Worksheets.Item(1)

$rows = $worksheet.range("D2").currentregion.rows.count

$worksheet.range("F2:F$rows").formula = '=AVERAGEIFS(SLA!$D:$D,SLA!$C:$C,"SG_SG_*_L_*STD",SLA!$B:$B,SLA!B2,SLA!$D:$D,"<>0")'

Sample Output

SYMM_ID         DATE                    INSTANCE        Total Response Time 
297900076   01-06-2022 05:00    SG_SG_ORACLUL_L_PRDPRF  0.31                 0.484210526
297900076   01-06-2022 05:05    SG_SG_ORACLUL_L_PRDPRF  0.5          0.843157895
297900076   01-06-2022 05:10    SG_SG_ORACLUL_L_PRDPRF  0.34                 0.569473684
297900076   01-06-2022 05:15    SG_SG_ORACLUL_L_PRDPRF  0.37                 0.551578947
297900076   01-06-2022 05:20    SG_SG_ORACLUL_L_PRDPRF  0.42                 0.578421053

CodePudding user response：

Your first criterion matches none of your data. You have "SG_STG_*_L_*STD", which doesn't match SG_SG_ORACLUL_L_PRDPRF. If no data matches the criteria you get division by 0.

I suppose you left out some bits from the code. I added

$worksheet.SaveAs(“C:\Test.xls”)
$excel.Quit()

to make it work.

I also wonder why you want the average repeated for each line in the sheet.