How to get the deviation.(Power Automate Desktop)
Introduction to obtaining deviation values in Power Automate Desktop.
Overall view of Flow
Power Automate Desktop alone is labor intensive, so an Excel sheet is utilized.
The results are set in a Data table.
Robin(for copy and paste)
It can be copied and pasted into Power Automate Desktop.
SET NumList TO [96, 91, 83, 75, 50]
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: False Instance=> ExcelInstance
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''Value''' Column: 1 Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''Deviation''' Column: 2 Row: 1
SET RowCounter TO 2
LOOP FOREACH CurrentItem IN NumList
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem Column: 1 Row: RowCounter
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: '=(' + CurrentItem + '-AVERAGE(A2:A' + (NumList.Count + 1) + '))/STDEV.P(A2:A' + (NumList.Count + 1) + ')*10+50' Column: 2 Row: RowCounter
Variables.IncreaseVariable Value: RowCounter IncrementValue: 1
END
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: 1 StartRow: 1 EndColumn: 2 EndRow: RowCounter - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
Flow creation steps
Prepare a List of values for which you wish to calculate deviations.
Set up Launch Excel Action and use with a blank document to create an empty Excel book.
Create the first row of an empty book created by setting up two Write to Excel worksheet Actions.
The value to be written is the column name. In the example, it is Value and Deviation, but it is arbitrary.
| Parameter | Value of the first Action | Value of the second Action |
|---|---|---|
| Value to write | Value | Deviation |
| Write mode | On specified cell | On specified cell |
| Column | 1 | 2 |
| Row | 1 | 1 |
Set up Set Variables and create a variable %RowCounter% and set the initial value to 2.
Then loop through List variable %NumList% with For each.(Specify List variable %NumList% for Value to iterate)
Place two Write to Excel worksheet Actions in For each.
| Parameter | Value of the first Action | Value of the second Action |
|---|---|---|
| Value to write | %CurrentItem% | %'=(' + CurrentItem + '-AVERAGE(A2:A' + (NumList.Count + 1) + '))/STDEV.P(A2:A' + (NumList.Count + 1) + ')*10+50'% |
| Write mode | On specified cell | On specified cell |
| Column | 1 | 2 |
| Row | %RowCounter% | %RowCounter% |
Then place Increase variable Action in For each to count up %RowCounter% by 1.
The For each part of the flow is complete. The flow is as shown below.
| Parameter | Value |
|---|---|
| Retrive | Value from a range of cells |
| Start column | 1 |
| Start row | 1 |
| End column | 2 |
| End row | %RowCounter – 1% |
| First line of range contains column names | ON |
Finally, close the book with Close Excel Action. There is essentially no need to save.
When this flow is executed, the Read from Excel worksheet Action The numbers and deviations are stored in the Variables produced (Variables name %ExcelData% by default).
As an example, the Data table below would look like this for %[96, 91, 83, 75, 50]%.
---