how to select a statistical range in an excel

0 votes
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 in Others by Kithuzzz
• 20,660 points
33 views

1 answer to this question.

0 votes

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))
answered Nov 4 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

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

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

answered Oct 24 in Others by narikkadan
• 37,660 points
40 views
0 votes
1 answer

How to multiply by a percentage range in Excel

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

answered Nov 8 in Others by narikkadan
• 37,660 points
32 views
0 votes
1 answer

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

answered Nov 17 in Others by narikkadan
• 37,660 points
28 views
0 votes
0 answers

How to open a new tab or a new window while writing an exam in AI - Onlime Remote Proctored

How to open a new tab or ...READ MORE

Jul 20, 2020 in Others by vimalkamal
• 120 points
2,485 views
0 votes
1 answer

Statistical Power of Sensitivity

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

answered Jul 13, 2018 in Data Analytics by CodingByHeart77
• 3,740 points
454 views
0 votes
2 answers

Significance of P-value in statistical data

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

answered Aug 10, 2018 in Data Analytics by Atul
• 180 points
466 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

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

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
397 views
0 votes
1 answer
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
33 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP