Average last n values based on criteria from 2 columns

[cojohnst5412]

I have 2 columns, col 1 has inputs (0.6, 0.5, 0.7, etc), and col 2 has binary results (0 or 1)... I want to average the "last 10" values from the results column, but only average if the input column is greater than 0.6...

The formula below works great, except it averages the last 10 values regardless if they are >0.60 or not, and I can't figure out a way to only average the last 10 if they meet the >0.6 criteria.

=ArrayFormula(iferror(average(query(if(len(A2:A),{ROW(A2:A),B2:B},),"Select Col2 where Col1>0.60 and Col2>-1 order by Col1 Desc limit 10"))))


(answers that work in Sheets as well would be fantastic)
Thanks in advance!

[FlameRetired]

Hello cojohnst5412. Welcome to the forum.

Perhaps you overlooked the 'gold' banner at the top of the page HOW TO ATTACH YOUR SAMPLE WORKBOOK:

In the meantime, in general terms, see if this helps.

Please Login or Register  to view this content.

[cojohnst5412]

I think I just uploaded a sample file, my apologies

[GeoffW283]

Dave has the answer, I think, in his #2 post - give or take a column number tweak in the second half of the formula:

Please Login or Register  to view this content.

[FlameRetired]

Now that I've seen your upload I also see that I may have interpreted wrong. Try this instead. In Excel 2013 you will have to array enter this:

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[FlameRetired]

BTW:
My first formula returns 0.611. My last one returns 0.4 which is what you indicate in your upload is expected.

[cojohnst5412]

Thank you so much for the help! Will work on testing this out soon...

I know it's an Excel forum, but by any chance is there a way this is portable to Google Sheets as well? I haven't tried an array formula Sheets before.

[FlameRetired]

I don't know. I am not familiar with Google Sheets.

There is a sub-forum here for other platforms Forum: For Other Platforms(Mac, Google Docs, Mobile OS etc)

Would you like me to move your thread there?

[cojohnst5412]

Seems to work on both, thanks so much for the help! Really appreciate it!

[FlameRetired]

You are welcome.

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

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.