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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Thanks for that.
How about
+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
Joined
Sep 12, 2017
Messages
12
Office Version
  1. 365

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 12, 2017
Messages
12
Office Version
  1. 365

ADVERTISEMENT

That's great, thank you for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Jonny_71

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


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
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
As this is now a completely different question, you will need to start a new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,126
Messages
5,570,332
Members
412,319
Latest member
akshat1231
Top