Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Average numbers associated to another column

0

I have a column "F" that contains "Date" and I have another column "AB" that contains the Differential [number].  In column F I have the last 20 most recent dates highlighted and what I would like to do is then highlight the lowest 10 associated [to the highlighted date] differential in column AB.  Since i have the conditional formatting set to auto only show the last 20 dates then I will want the differential to auto change as the dates change.  Once I can get those differential numbers I need to average them.

Answer
Discuss

Discussion

Did you see my Answer below? Seems that the email alerts stopped working (following a site update) so you may have missed it.
John_Ru (rep: 6092) May 6, '22 at 12:31 pm
Add to Discussion

Answers

0

Hi and welcome to the Forum

You can use the LARGE function but with an array of numbers 1-10 (in curly brackets)- that will return the 10 highest number (most recent dates) in column F.

Those numbers can be used in MATCH to get the rows in column F which are then used in INDEX to get the values from cells in the 23rd column of the range F:AB (since the 1st column of that range is F and AB is the 23rd).

Finally those values are then caculated in the AVERAGE formula.

It's an array formula so in Excel 365 you just need to paste this in a cell:

=AVERAGE(INDEX(F:AB,MATCH(LARGE(F:F, {1,2,3,4,5,6,7,8,9,10}),F:F,0),23))

In earlier versions of Excel, you'll need to use Ctrl + Shift + Enter to input the formula.

If you also need to highlight the differential numbers associated with the 10 highest dates in column F say, you can create a new rule for cell AB2, based on value and use this formula (note there's only a single $ sign to lock the column to F but not the row):

=IF(RANK.EQ($F2,F:F,0)<=10,1,0)

and set a fill then edit the Applies to field to cover the contents of AB.

If there's existing conditional formatting there (say Top 20), you'd need to 'promote' the new rule so it appears above the existing rule (so that your new formatting appear on top ). Just change the 10 in bold above to give a different set (e.g. 3 for the differentials associated with the 3 highest dates)

Altermatively could use the LARGE function to create a series of rules, this being one to identify the cell in AB relating to the 2nd (highest) date:

=IF($F2=LARGE(F:F, 2),1,0)

Hope this works for you.

Discuss

Discussion

I revised my answer to cover conditional formatting. If it doesn't make sense, please attach a representative Excel file to your original question (using the Add Files button which appears below the text when you choose to Edit the question). I'll then apply the CF.
John_Ru (rep: 6092) Apr 23, '22 at 4:59 am
Did that work for you?
John_Ru (rep: 6092) Apr 26, '22 at 9:20 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login