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

Google Spreadsheet Create a graph of top 5 text strings

Question:

( by Laurie Tewksbury )

How can I count how often text occurs, then create a graphic to shop top the 5 text strings?
I have a list of usernames in a spreadsheet as they contribute to our social media site. I'm hoping to take that list of usernames and find a way to graphically represent the top 5 contributors. A pie graph would be ideal, but a list would work well, too. I'd love for it to update automatically as users continue to contribute to our site. Therefore, it'll always show the top 5 contributing users, but these will change as users contribute more or less.

Is this possible? THANKS!

Solution:

Have a look at the following screenshot of "Sheet1":

Have a look at the following screenshot of "Sheet2":

In the above sheet "Sheet2" I have the following formula in Cell A1:

=query('Sheet1'!A:C;"select C,count(B) where C<>'' group by C order by count(B) desc limit 5 label count(B) 'Total Participation' ";1)

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

Google Spreadsheet Formula to return only row 1 values

Question:

( by malva00 )

Example: A1 has "Event 1", B1 has "Event 2", C1 has "Event 3." 

Let's say "John" is listed somewhere (could be anywhere) under Event 1 and 3, but not 2. I want a single formula to return all the events John is listed under. If the formula is in G1, I want it to return "Event 1" in G1 and "Event 3" in H1 (Event 2 is skipped).

Any help? :D

Solution:

Have a look at the following screenshot of Spreadsheet:

In the above sheet I have the following formula in Cell G1:

=split(if(countif(A:A;"John");A1;"")&" "&if(countif(B:B;"John");B1;"")&" "&if(countif(C:C;"John");C1;"")&" ";" ")

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

Google Spreadsheet Query function to count rows

Question:

( by Mo S )

Here's what my sheet looks like (based on form responses).

Person	Availability	STATUS
Bob	Mondays, Tuesdays	approved
Joe	Mondays, Wednesdays
Fish	Mondays, Tuesdays, Wednesdays	approved

Here's what i want the results to look like:

RESULTS (on another sheet))
Mondays	Tuesdays	Wednesdays
2	2	1

Basically, the query function needs to count how many ppl are available on those days that are also approved.

The query function i currently use does not consider the 3rd column becuz i dont know how to make it:s

Any help appreciated. Thanks.

I am using firefox/win 7

Solution:

Have a look at the following screenshot of Sheet "Sheet1":

Have a look at the following screenshot of Sheet "Sheet2":

In the above sheet I have the following formula in Cell A2:

=arrayformula( countif( if( 'Sheet1'!C:C = "approved" ; 'Sheet1'!B:B ; "" ) ; "*"&A1:C1&"*" ) )

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

Google Spreadsheet Conditional Formatting Counting Commas

Question:

( by Aram Donabedian )

I was wondering if there was a way to assign conditional formatting based on the number of comma separators in a cell?  IE: A,=green B,C,=Blue D,E,F,=Yellow

The letter values won't always be fixed and will be different from cell to cell so I want the number of commas in the cell to be the condition.

Can I do that?
Thanks

Solution:

Have a look at the following animated screenshot:

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

function onEdit(e) {
  condition();
};

function condition() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  if(s.getName() == "Sheet1")
  {
    var cell = s.getActiveCell();
    var a1_Notation = cell.getA1Notation();
    if(a1_Notation=="A1") {
      var str = cell.getValue().toString();
      var count = str.split(",").length - 1;
      if(count==1) cell.setBackground("lightgreen");
      else if(count==2) cell.setBackground("blue");
      else if(count==3) cell.setBackground("yellow");
      else cell.setBackground("white");
    }
  }
};

///////////////////////////////////////

put the above code in your script editor and then whenever you edit on cell "A1" of sheet "Sheet1", this script will count the number of comma and place the color accordingly.


And If you are not much familiar with scripts then check out the following link:http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

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

Google Spreadsheet Mutli Lookup formula

Question:

( by George Randell )

Using Windows 7 on Firefox

I'm probably being dim but I can't for the life of me workout/remember how to set up a function that using a lookup table can work out via column and row from 2 other cells

EG.  
main sheet

a          b         c          d         
name   item    thing     (%) <-- whats outmanuvering me at the moment

Lookup table
         thinga thingb thingc
itema  5%      10%    13%
itemb  6%      11%     15%
itemc  7%      13%     20%

column b and c a already data validated as lookups for (b = item colum and c = thing row) I'm after the formla to 'add' column b and c so d automatically looks up the percentage relating to both.
I have a funny feeling this is alot simpler than i'm making it..any help would be much apreciated

Solution:

Have a look at the following screenshot of Sheet "Lookup Table":

Have a look at the following screenshot of Sheet "Main":

In the above sheet I have the following formula in Cell D1:

=query(transpose(query('Lookup Table'!A1:D4;"select * where A = 'item"&B1&"'";1));"select Col2 where Col1 = 'thing"&C1&"'";0)

In the above formula change "Lookup Table" to the name of the sheet in which you have the lookup table data.

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

Google Spreadsheet Script to update time when changes are made on sheet

Question:

( by Henry Mitchell )

Hello,

I was wondering how to update the time for each  individual sheet when changed. Currently I have a GoogleDoc with 4 sheets. I have put the formula "=Now()" on all sheets, but when one is changed, they all update the time. I want to know how to get the time to update for just the sheet modified.

Thanks

Solution:

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan
// For more visit: iGoogleDrive.blogspot.com

function onEdit(e) {
  timeupdate();
};

function timeupdate() {
  var date = Utilities.formatDate(new Date(), "GMT-0500", "MM/dd/yyyy hh:mm:ss");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getRange("A1").setValue(date);

};

///////////////////////////////////////

put the above code in your script editor and then whenever you edit on any sheet, it will automatically update the current time on Cell "A1" of that sheet.

You can change the cell "A1" as per your requirement. And also adjust the timezone "GMT-0500" as per your requirement.


And If you are not much familiar with scripts then check out the following link:http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

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