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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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))
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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.
 
Upvote 0
As this is now a completely different question, you will need to start a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,737
Members
448,295
Latest member
Uzair Tahir Khan

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