Google Spreadsheet Calculations with last N rows

Question:

( by lunixer )

Here's a formula I want to calculate:

In column B I have distance. In column C I have minutes and in column D I have seconds.

I want to sum the last 21 rows of B and divide it by the sum of the last 21 rows of C and D converted to hours. I can do the conversion bit (C/60+D/3600) but I'm not quite sure how I would capture the last 21 rows in that way.

Thanks!

Solution:

Have a look at the following screenshot of Spreadsheet:

In the above sheet, I have the following formula in Cell F3:

=round( query( query( arrayformula( if( {1,1,1,0} ; B2:D ; row(B2:B) ) ) ; "select Col1,Col2,Col3 where Col1 is not null order by Col4 desc limit 21" ; 0 ) ; "select sum(Col1)/((sum(Col2)/60)+(sum(Col3)/3600)) label sum(Col1)/((sum(Col2)/60)+(sum(Col3)/3600)) '' " ; 0 ) ; 2 )

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive