Average amount of latest 4 values based on criteria

I have 2 excel sheets. Sheet 1 have following records:

Salon coach date amount
Sal1 col1 11/7/2018 500
Sal1 col1 11/1/2018 400
Sal1 col1 10/25/2018 600
Sal1 col1 10/19/2018 700
Sal1 col1 10/1/2018 250
And so on
Sheet 2 have following cloumns and records:

Salon coach average of lastest 4 dates
Sal1 col1 ???
I want to find average amount for 4 latest dates with match the criteria salon and coach. Thanks in advance.

Hi goodyguyrulz. Welcome to the forum.

Try

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H
1	Salon	coach	date	amount		Salon	coach	average
2	Sal1	col1	11/7/2018	500		Sal1	col1	550
3	Sal1	col1	11/1/2018	400
4	Sal1	col1	10/25/2018	600
5	Sal1	col1	10/19/2018	700
6	Sal1	col1	10/1/2018	250

FlameRetired,

Thank you for the message, already tried this works if we have one salon and one coach. If new salon comes it doesnt show correct values

Then we need an upload of a spreadsheet that is representative of the real issues. Something that includes a representative set of what you are working with and a section (hand typed if necessary) showing the layout of the desired outputs.

If you are not familiar with how to do this know that the 'paperclip' icon has not worked for a long time. Instead:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

You appear to have shifted the goalposts - is it the 4 latest dates or the 4 latest dates excluding the latest, and if the latter, does this mean the average of 3 or 4 dates? What you have failed to do is provide the answers you are expecting, manually calculated.

I am really sorry for the confusion. these are 4 latest dates excluding the most latest date and this means average of 4 dates. lets suppose we have 5 dates it needs to calculate average of 4 dates starting with 2nd most recent date to 5th date.

updated file with expected output is attached.

Thanks for the clarification.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Still a bit confused, but this gives your expected result. Check it carefully, as your sample numbers may not highlight errors...

=AVERAGE(IF((Table1[Salon]=F3)*(Table1[coach]=G3)*ROW(Table1[amount])>=LARGE((Table1[Salon]=F3)*(Table1[coach]=G3)*ROW(Table1[amount]),MIN(4,COUNTIFS(Table1[Salon],F3,Table1[coach],G3))),Table1[amount],""))

This is an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

No. I think that it is wrong. Please update your sample with DIFFERENT numbers of rows for each combination (currently they are all n=5) along with the manually calculated expected answer

This will work... provided that the dates within each group combination are ALWAYS in descending order. If not, please supply a REALISTIC and REPRESENTATIVE sample.

There's bound to be a better way to do this.... but I'm not seeing it yet!! BtW, it will still work if there are fewer than 4 valid values to average.

Formula:

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Irrespective of order in date formula gives the average required.

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.