Last 20 dates

tbilderbac

New Member
Joined
Apr 11, 2022
Messages
28
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I am have a list of dates and I am trying to extract the last 20 dates and then I want to look at the Diff column numbers [that is associated to the date] and get the average of the 10 lowest differentials out of the last 20 dates.

Date Diff
4/3/2022 24
4/5/2022 19
4/6/2022 23
4/7/2022 24
4/8/2022 26
4/9/2022 25
4/10/2022 28
4/11/2022 21
4/28/2022 19
4/29/2022 24
5/4/2022 15
5/5/2022 16
5/9/2022 30
5/10/2022 26
5/11/2022 25
5/13/2022 24
6/5/2022 19
6/6/2022 21
6/7/2022 20
6/12/2022 26
6/13/2022 21
6/14/2022 24
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
How about
+Fluff 1.xlsm
ABCD
1
203/04/20222419.9
305/04/202219
406/04/202223
507/04/202224
608/04/202226
709/04/202225
810/04/202228
911/04/202221
1028/04/202219
1129/04/202224
1204/05/202215
1305/05/202216
1409/05/202230
1510/05/202226
1611/05/202225
1713/05/202224
1805/06/202219
1906/06/202221
2007/06/202220
2112/06/202226
2213/06/202221
2314/06/202224
Data
Cell Formulas
RangeFormula
D2D2=AVERAGE(INDEX(SORT(INDEX(SORT(A2:B23,1,-1),SEQUENCE(20),2),,1),SEQUENCE(10)))
 
Upvote 0
So can you explain this to me? Is this looking at column A and pulling the LAST 20 dates and then pulling the LOWEST 10 Diffs from Column B? Then averaging the 10 Diffs to get the number 19.9? Just want to make sure I understand what I am looking at? thanks
 
Upvote 0
Yes that's exactly what it doing. :)
 
Upvote 0
Awesome, thank you and happy I was able to decipher what it was saying.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
So I want to use conditional formatting to highlight those 10 diffs in the column and I am getting a warning:
1650550009225.png

How might I format the column so highlight the 20 dates and also highlight the 10 diffs. The columns are not next to each other so I cant highlight the whole row. I will need to have twp formulas [or maybe 1] and create two rules.
 
Upvote 0
As this is a totally different question, it needs a new thread. Thanks
 
Upvote 0
Okay I will do that, sorry but I do have a question because I have added in a number of additional dates and indexs and the average does not seem to be updating? I have gone in and refreshed it but is seems to be staying the same?
 
Upvote 0
Have you changed the range to suit?
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top