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

#### Jonny_71

##### New Member
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Fluff

##### MrExcel MVP, Moderator
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

#### Jonny_71

##### New Member
Just updated, thanks.

#### Fluff

##### MrExcel MVP, Moderator
Thanks for that.
+Fluff v2.xlsm
ABCDE
1
201/01/2020apples5
301/01/2020pears1204/01/2020
401/01/2020oranges4Apples11.333333
501/01/2020bananas9
601/01/2020peaches6
702/01/2020apples2
802/01/2020pears1
902/01/2020oranges14
1002/01/2020bananas7
1102/01/2020peaches4
1203/01/2020apples6
1303/01/2020pears6
1403/01/2020oranges18
1503/01/2020bananas19
1603/01/2020peaches5
1704/01/2020apples13
1804/01/2020pears9
1904/01/2020oranges8
2004/01/2020bananas4
2104/01/2020peaches2
2205/01/2020apples5
2305/01/2020pears3
2405/01/2020oranges17
2505/01/2020bananas22
2605/01/2020peaches10
2706/01/2020apples16
2806/01/2020pears11
2906/01/2020oranges8
3006/01/2020bananas7
3106/01/2020peaches3
Main
Cell Formulas
RangeFormula
E4E4=AVERAGEIFS(C2:C31,B2:B31,D4,A2:A31,">="&E3)

Where E3 is the date you want to count from.

#### Jonny_71

##### New Member

Thank you for that. Sorry to complicate things but if I, for example, I wanted to return a value in wanted to return the answer in cell E27 on 06/01/20 for the average of the last 3 quantities of apples from the dates above in cells C2:C26 how would I do that?

So the answer in cell E27 would be 8 (the average of the most recent 3 values for apples 03/01/20, 04/01/20 and 05/01/20).

Hope you follow what I mean...

#### Fluff

##### MrExcel MVP, Moderator
Is this what you mean
+Fluff v2.xlsm
ABCD
1
201/01/2020apples5
301/01/2020pears12
401/01/2020oranges4
501/01/2020bananas9
601/01/2020peaches6
702/01/2020apples2
802/01/2020pears1
902/01/2020oranges14
1002/01/2020bananas7
1102/01/2020peaches4
1203/01/2020apples6
1303/01/2020pears6
1403/01/2020oranges18
1503/01/2020bananas19
1603/01/2020peaches5
1704/01/2020apples13
1804/01/2020pears9
1904/01/2020oranges8
2004/01/2020bananas4
2104/01/2020peaches2
2205/01/2020apples5
2305/01/2020pears3
2405/01/2020oranges17
2505/01/2020bananas22
2605/01/2020peaches10
2706/01/2020apples168
2806/01/2020pears116
2906/01/2020oranges814.33333
3006/01/2020bananas715
3106/01/2020peaches35.666667
Main
Cell Formulas
RangeFormula
D2:D31D2=IF(A2<>MAX(A:A),"",AVERAGEIFS(C:C,B:B,B2,A:A,">="&A2-3,A:A,"<"&A2))

#### Jonny_71

##### New Member

That's great, thank you for your help.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

#### Jonny_71

##### New Member

Hi,

Apologies but complicating it with another example now...

In the example above, in cell F26 if I wanted to calculate the average number of goals scored by Team A in the preceding three weeks what would the formula be?

So the answer would be 0.67 (average of 1 + 1 + 0) for the previous three weeks. I've tried but I just can't get it right.

Thank you.

#### Fluff

##### MrExcel MVP, Moderator
As this is now a completely different question, you will need to start a new thread. Thanks

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.

1,164,113
Messages
5,835,456
Members
430,357
Latest member
Greshapa

### 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.

### Which adblocker are you using?

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

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