Google Spreadsheet Area Calculations in Feet and Inches

Question:

( by Paul Enestvedt )

How do I enter feet and inches in a spreadsheet so that I can multiple values together and arrive at a sq. ft. (L x W) value?

Solution:

Have a look at the following screenshot:

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

=round( ( (A3*12)+B3 ) * ( (D3*12)+E3 ) / 144 ; 2 )

and then you can drag the above formula to Cell G4,G5... and so on... as far as needed...

And you can also use the following array formula in Cell G3 to auto populate entire column G:
=arrayformula( if( A3:A="" ; "" ; round( ( (A3:A*12)+B3:B ) * ( (D3:D*12)+E3:E ) / 144 ; 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 How do you subtract/add values from a cell much like depositing and withdrawing from a bank account? (Not just + and - operators)

Question:

( by Tes Lescat )

Say I have a cell (let's say A1) with value 1000

From another cell (B1), I want users to input X so that A1 will become 1000+X. From that same cell B1, if a user were to input -Y, A1 does not go back to 1000 but rather adds or subtracts all the values that have ever been entered into A1. Thus, A1 would be 1000+X-Y

For example:

A1 = 500

user enters B1 = 4

A1 = 504

user enters B1 = -20

A1 = 484 (because 504-20, not 500-20)

user enters B1 = 100

A1 = 584

etc. etc.

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) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var a1 = s.getRange("A1");
  var b1 = s.getRange("B1");
  var activeCell = s.getActiveCell().getA1Notation();
  if( s.getName()=="Sheet Name" && activeCell=="B1" )
    a1.setValue(a1.getValue()+b1.getValue());
};

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

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

You can change the "Sheet Name, "A1" and "B1" 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

Google Spreadsheet Concatenate 2 rows if they have the same value in one column

Question:

( by Paul Enestvedt )

Hi, I'm trying to merge the text in several rows based on a common ID# in a different field.  For example, I have:

Id           date             service

12345     1/21/2012     testing

23456     4/21/2010     registration

12345     2/15/2012     conference

12345     2/15/2013     practice test

44567     9/14/2009     coaching

12345     8/15/2012     employed

44567     10/13/2010   conference

etc.

I would like:

12345     testing, conference, practice test, employed

23456     registration

44567     coaching, conference

I found a post that demonstrated a similar query for summing, but I want to concatenate.

Anyone ideas?

Thanks,

Paul

Solution:

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

And following screenshot of "Sheet2":

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

=unique('Sheet1'!A2:A)

the following formula in Cell B2:

=join(", ";transpose(iferror(filter('Sheet1'!C:C;'Sheet1'!A:A=A2))))

and then you can drag the above formula to Cell B3, B4... and so on... as far as needed...

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

Change Background Color Of Google Document

Follow the steps that are shown below, to change the color of Google Document.

Go to menu "File" > "Page setup...":

Then choose the "Page color" that you want in background:



And then click "OK".

Final result:

And you are done!

Hope this helps.

Google Spreadsheet Script to Change Color of Cell(s) Having Minimum Value

Question:

( by Kaido Põder )

I have range A1:A9 numbers. In Google Spreadsheet, How can I change color of cell having minimum value.

Solution:

Have a look at the following screenshot:

Have a look at the following code:

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

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

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Sheet1");
var r = s.getRange("A1:A9");

function onEdit(e) {
  var activeSheet = ss.getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  var sortedValues;
  if( s.getName() == activeSheet.getName() &&
    activeRange.getLastRow() >= r.getRow() && 
    activeRange.getRow() <= r.getLastRow() &&
    activeRange.getLastColumn() >= r.getColumn() && 
    activeRange.getColumn() <= r.getLastColumn() )
    { 
      check();
    }
};

function check() {
  var v = r.getValues();
  var minValue;
  var flag=false;
  
  for(var i=0;i<v.length;i++)
    for(var j=0;j<v[0].length;j++) {
      if(v[i][j]!="" && flag==false) {
        minValue=v[i][j];
        flag=true;
      }
      else if(v[i][j]!="" && minValue>v[i][j]) minValue=v[i][j];
    }
  
  var rowcol = [];
  for(var i=0;i<v.length;i++)
    for(var j=0;j<v[0].length;j++)
      if(minValue == v[i][j])
        rowcol.push([i,j]);
  
  r.setBackground("White");
  for(var k=0;k<rowcol.length;k++)
    s.getRange(r.getRow()+rowcol[k][0],r.getColumn()+rowcol[k][1]).setBackground("Yellow");
};

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

put the above code in your script editor and change Sheet1 with your sheet name and A1:A9 with the range that you want to include to check for the minimum value. If you want to check for whole column A then put "A:A" and if you want to check for column A,B,C then put "A:C", so it will work for any range you provide...


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 Countif with multiple criteria

Question:

( by Ar974 )

Bonjour, Hello

example:

cells  :            ListPos= UH/UV/S1/S2/DC/P/RH/RS/GRE/FG/JK/ML/DE/CV/........  (From S1 to S25......)

another List    ListC= CA / RTT /P / CET....

column A :      date : 1 janv / 2 janv / 3 janv / 4 janv / 5 janv / 6  janv / 7 janv /........

column B:  Position : UH     / DH     /         / S1      / CA      / RTT     /           /......... :  ----> cell can be clean /  can be fill from values from ListC or ListPos

so  B column B has elements from the ListPos and ListC with different dates

I want to make the sum of numbers of these positions:   S1 UH S2

then my formula will be:    =COUNTif(B6:B40;"S1")+COUNTif(B6:B40;"UH")+COUNTif(B6:B40;"S2") this is ok

but if I want X criteria (more than 10 for example):  =COUNTif(B6:B40;"UH")+COUNTif(B6:B40;"UV")+COUNTif(B6:B40;"S1")+COUNTif(B6:B40;"S2")+COUNTif(B6:B40;"DC");;;;; IT IS VERY LONG .....AND TAKE TIME

Is there no a formula like that :   =COUNT(FILTER(B6:B40 ; B:B="S1:S25")) ???? COUNT(FILTER(B6:B40 ; B:B=ListPos)) ??? not working to. ????

Merci a l'avance.

Thanks for your help in advance.

Solution:

Have a look at the following screenshot:

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

=counta( iferror( filter( B6:B40; match(B6:B40;S1:S25;0) ) ) )

and the following formula in Cell E1:

=counta( iferror( filter( B6:B40; match(B6:B40;S1:S25;0) ) ) ) + counta( iferror( filter( B6:B40; match(B6:B40;T1:T25;0) ) ) )

and the following formula in Cell F1:

=counta(arrayformula(iferror(match(B6:B40;S1:S25;0);iferror(match(B6:B40;T1:T25;0)))))

In the above formulas, I have assumed that you have you have the ListC in Column T (that is T1:T25) you can change the range as per your requirement.

And if you have given the range "S1:S25" name as "ListPos" then you can replace it in the above formula

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 Group By

Question:

( by Mikkel Holm )

Hi,

I'm organizing the amount of sales for my company in a spreadsheet, and I would like to see how much provenue every agent generates.

I know to count the amount of orders pr agent, by using the countif formula, but I'm having a hard time figuring out how to sum the value of orders made by a specific agent.

In other words I'm searching for a formula to sum the values of column B if column A is "XX" in column F. For example in F2 should be the summed value of all the cells i column B, where the initials "MKH" is in the adjacent A cell.

	A	B	C	D	E	F
1	Name	Value	Occurrences pr name		Value pr name
2	MKH	100	MKH	4	MKH	?
3	LT	200	LT	3	LT	?
4	LT	200
5	LT	200
6	MKH	100
7	MKH	100
8	MKH	500

Does anyone have a good idea? Your help will be much appreciated.

Cheers

Mike.

Solution:

Have a look at the following screenshot:

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

=query(A:B;"select A,count(B),sum(B) where A<>'' group by A")

and if you want to label the column header then try the following formula:

=query(A:B;"select A,count(B),sum(B) where A<>'' group by A label count(B) 'Count',sum(B) 'Sum'")

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 Send Email only Active Sheet as PDF Attachment

Question:

( by Brian Galloway )

I use google docs to share with all of my employees our day to day schedule but I also like to email myself the pdf of the particular day.  The only thing when I send myself the pdf, it sends me a pdf with every sheet.  I am trying to figure a way to just send myself the sheet of the particular day. Does anyone have any ideas how to accomplish this?
Cheers, Brian

Solution:

Have a look at the following code:

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

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

function onOpen(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Send ActiveSheet as PDF", functionName: "SendSheetAsPDF"});
  spreadsheet.addMenu("Send Email", menuEntries);
};

function SendSheetAsPDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId(); 
  var sheet = ss.getActiveSheet();
  var email = "kishan.pionero@gmail.com";
  var subject = "SUBJECT HERE..!!";
  var body = "Body of email here..!!";
  var getSheetId = sheet.getSheetId().toString();
  var sheetName = sheet.getName();
  
  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/");
  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  var requestData = { "method": "GET","oAuthServiceName": "google","oAuthUseToken": "always" };
  
  var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+ssID+"&gid="+getSheetId+"&portrait=true"+"&exportFormat=pdf";
  var result = UrlFetchApp.fetch(url , requestData);
  var contents = result.getContent();
  
  MailApp.sendEmail(email,subject,body,{attachments:[{fileName:sheetName+".pdf",content:contents,mimeType:"application//pdf"}]});
};

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

put the above code in your script editor, save it and then refresh your spreadsheet. You'll be able to see the custom menu "Send Email" under which you'll have sub menu "Send ActiveSheet as PDF".

Have a look at the following animated screenshot:

So now, when you click on this sub menu, you will get an email on the email Id provided with the active sheet as attachment in PDF format.

In above code change the email Id "kishan.pionero@gmail.com" to your email Id.


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