Formula to work out the average value of the most recent entries

Jonny_71

New Member
Joined
Sep 12, 2017
Messages
12
Office Version
  1. 365
Hi,

Please can anybody help as I'm really stuck with this?

I need a formula that will work out the average value of the three most recent entries in the table below for each fruit. In the table below, the date is in column A, the fruit is in column B, and the quantity is in column C. Each fruit has 6 different entries, one entry for each day from 01/01/20 to 06/01/20 from row 2 to row 31.

If I want to work out the average quantity of apples from the 3 most recent dates how do I do this? I know the answer is 11.33 (the average of the 3 values from 04/01/20, 05/01/20 and 06/01/20) but I don't know how to write a formula that will give me the answer.

Any help is greatly appreciated.

Thank you.


Date Fruit Quantity
01/01/2020 apples 5
01/01/2020 pears 12
01/01/2020 oranges 4
01/01/2020 bananas 9
01/01/2020 peaches 6
02/01/2020 apples 2
02/01/2020 pears 1
02/01/2020 oranges 14
02/01/2020 bananas 7
02/01/2020 peaches 4
03/01/2020 apples 6
03/01/2020 pears 6
03/01/2020 oranges 18
03/01/2020 bananas 19
03/01/2020 peaches 5
04/01/2020 apples 13
04/01/2020 pears 9
04/01/2020 oranges 8
04/01/2020 bananas 4
04/01/2020 peaches 2
05/01/2020 apples 5
05/01/2020 pears 3
05/01/2020 oranges 17
05/01/2020 bananas 22
05/01/2020 peaches 10
06/01/2020 apples 16
06/01/2020 pears 11
06/01/2020 oranges 8
06/01/2020 bananas 7
06/01/2020 peaches 3
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,110
Messages
5,835,439
Members
430,357
Latest member
RadStorm20

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
Top