Thursday, September 19, 2013

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:
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,

1 comment:

  1. Thanks. I simply want to add up the last 30 cells in column. Thoughts?

    ReplyDelete