Execute Excel formula using powershell

Question

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

Palle Due · Accepted Answer · 2022-07-14 08:46:01Z

1

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.

answered Jul 14, 2022 at 8:46

Palle Due

6,3224 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Empty Coder Over a year ago

@PalleeDue: Yeah I got it fixed and data is coming. but the problem is the data is coming same for all the rows same as the first one

Palle Due Over a year ago

Of course it is. You are calculating an average of a column and putting it into each row. Did you expect each row to have another average?

Empty Coder Over a year ago

Yes. it should calculate for each row. please let me know what change I have to make

Palle Due Over a year ago

If you want the average of total response time for each row, you already have it. I can't imagine that you want the average of one number, so please specify what you expect.

Empty Coder Over a year ago

Now for the first row in my sample data 0.31, for that I am getting value like 0.064. for the next i.e. 0.5 the AVG calculation value should be different right and so on..

|

Collectives™ on Stack Overflow

Execute Excel formula using powershell

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related