I have a large array of numbers. These readings were taken every day for four months. One gadget is represented by each row. I must determine the trendline and disregard the outliers. I made an effort to determine the IQR, then the range, and finally a filter to remove outliers that fall beyond the range. the problem is that there are a lot of repetitive results so the q1 and the q3 could very easily be the same number. is there a way to select the central 90% of results in a range? let's say from  [1,1,1,1,1,1,1,1,2,3,4,5,7,8,9,9,9,9,9,9] Select [1,1,1,1,1,1,1,2,3,4,5,7,8,9,9,9,9,9] to make life harder, I'd also like to be able to select the associated date with the day the measurement was taken. the dates are in a separate row.
Nov 3, 2022 in Others 293 views

## 1 answer to this question.

The lowest 5% and top 5% will not be included in the dynamic array that this formula returns. Your data range is assumed to be A1–A20.

`=FILTER(\$A\$1:\$A\$20,(ROW(\$A\$1:\$A\$20)>0.05*COUNT(\$A\$1:\$A\$20))*ROW(\$A\$1:\$A\$20)<0.95*COUNT(\$A\$1:\$A\$20))`
• 63,180 points

## How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

## How to multiply by a percentage range in Excel

The following would yield an array from ...READ MORE

## How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

## How to save a new sheet in an existing excel file, using Pandas?

import pandas as pd import numpy as np path ...READ MORE

## Statistical Power of Sensitivity

Sensitivity is commonly used to validate the ...READ MORE

## Significance of P-value in statistical data

Refer the following points explaining the significance ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE