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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,212,927
Messages
6,110,730
Members
448,294
Latest member
jmjmjmjmjmjm

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