How to get the median.(Power Automate Desktop Action)


Japanese version.

Introduction to how to get the median value in Power Automate Desktop.

Overall view of Flow

Robin(for copy and paste)

It can be copied and pasted into Power Automate Desktop.

SET NumList TO [5, 10, 30, 100]
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: False Instance=> ExcelInstance
SET RowCounter TO 1
LOOP FOREACH CurrentItem IN NumList
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem Column: 1 Row: RowCounter
    Variables.IncreaseVariable Value: RowCounter IncrementValue: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: '=MEDIAN(A1:A' + NumList.Count + ')' Column: 2 Row: 1
Excel.ReadFromExcel.ReadCell Instance: ExcelInstance StartColumn: 2 StartRow: 1 ReadAsText: False CellValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance

Flow creation steps

Prepare a list of numbers for which you wish to calculate the median.

This Flow requires a lot of work using Power Automate Desktop alone, so an Excel sheet is utilized.

Set up an Excel Launch Action and create an empty Excel book.

Launch Excelwith a blank document
Make instance visibleOFF

Create a RowCounter by placing a Set Variable Action and set the initial value to 1.

Loop through List variables with For each.
Specify the List variable as the Value to be iterated.

Set up a Write to Excel worksheet Action in For each.

Value to write%CurrentItem%
Write modeOn specified cell

Set up an Increase variable in For each to count up RowCounter by 1.

Set up a Write to Excel worksheet Action outside of For each

The value to be written is the formula for the MEDIAN function to obtain the median value in Excel.

Value to write%'=MEDIAN(A1:A' + NumList.Count + ')'%
Write modeOn specified cell

Retrieving the result of this formula with Read from Excel worksheet Action will take the median into the variable.

RetrieveThe value of a single cell
Start column2
Start row1

Finally, close the book with Close Excel Action. There is essentially no need to save.

When this flow is executed, the median value is stored in the Variables produced in Read from Excel worksheet Action.

As an example, the following results are obtained when running for %[5, 10, 30, 100]%.