I am looking to average 10 of the last 20 values based on a date. Column B contains dates, not specifically in order from oldest to current, and column C contains numeric values. I am wanting to average the 10 lowest values in column C based on the most current 20 dates in column B. Can anyone help with this?
It would be very helpful for us if you include a sample (in excel format)
Here is a sample of what I will have. there are 30 dates with corresponding values, and I want to average the 10 lowest values based on the 20 most recent dates.
Can anyone help on this?
Hoping this is somewhat what you want.. run the macro
Can you sort the data by date in either ascending or descending order?
Will there always be at least 20 dates and numbers to get the average from? If not, what should happen?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
the data is input from a used form, that is why it is not sorted. if I can get the formula to work for 20 dates, I can make the adjustments for less.
Not sure if this is what you want but you can try it and see.
Array entered**:
=AVERAGE(SMALL(IF(A2:A31>=LARGE(A2:A31,20),B2:B31),{1,2,3,4,5,6,7,8,9,10}))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Good one, Tony...
Thanks!
Hoping that's what the OP was looking for.
That works perfectly, thanks. I modified it to work for less then 20 dates and number of values to be averaged as I needed and it all works great. Thanks for your help Tony!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks