Google Spreadsheet Formula to return a min and max time value

Question:
 
( by mykarz )



I've been trying every which way to get something that would seem simple enough to do, to actually work.

I want to see the minimum time of a series of times using the m:ss.xxx format, as well as see the maximum of the same set, yet whenever I run a "min"/"mina" or "max"/"maxa" function, I always get 0 as a return.  So this means that my inputs are not viewed as numbers, but as text?  

Any help would be appreciated! :)

Solution:

First of all, format your series of times as "Plain Text".
Have a look at the following link to format range as a "Plain text":
http://igoogledrive.blogspot.in/2012/09/Format-range-as-Plain-text-in-Google-Spreadsheet.html

Now, have a look at the following screenshot:

Put the following formula in the cell "D5" for the min value:

=IFERROR(QUERY(ArrayFormula(A:A);"select Col1 where Col1 <> '' order by Col1 limit 1"))

Put the following formula in the cell "D6" for the max value:

=IFERROR(QUERY(ArrayFormula(A:A);"select Col1 where Col1 <> '' order by Col1 desc limit 1"))

In the above formulas replace the range ( or column ) "A:A" according to your requirement.

Or alternatively you can also try the following formulas:

For min value:

=IFERROR(QUERY(A:A;"select A where A <> '' order by A limit 1"))

For max value:

=IFERROR(QUERY(A:A;"select A where A <> '' order by A desc limit 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 or confidential projects:

http://igoogledrive.blogspot.in/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 Email notification on Form Submission

Question:

I've created a form to embed on my website and would love to know if there is a way to have email notification each time someone submits a complete form so that I don't have to monitor the spreadsheet on a daily basis.

Solution:

Go to "Notification rules..." in "Tools" menu:

Have a look at the following screenshot:

And then select for "A user submits a form", 
and also select for "Email - right away"

Have a look at the following screenshot:

Now, you will receive email notifications each time a user submits response to your form.


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 or confidential projects:

http://igoogledrive.blogspot.in/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 calculate score

Question:

( by Kenny.in.da.house )

Hi all,

Need help to write a formula for a badminton scoreboard.

Logic: [Lookup] player name on the data table + if match [countif] or [sumif] player's total won/lost + total points won/lost

Attached herewith my worksheet:

I have manually calculated the results from the data table on the left side. Highlighted in RED colour cell with my question in comment.

Appreciate if anyone could shed me some light on which syntax to use and how to write the appropriate formula.

Many thanks in advance.

Cheers,

Kenny

Solution:

You have to install the VMerge script that is available in the script gallery.

Or here is the code for that (copy paste it in "Tools" menu >> "Script editor..."):

function VMerge() { 
  var maxw=l=0;
  var minw=Number.MAX_VALUE;
  var al=arguments.length ;
  for( i=0 ; i<al ; i++){
    if( arguments[i].constructor == Array )l =arguments[i][0].length ;
    else if (arguments[i].length!=0) l = 1 ;  // literal values count as array with a width of one cell, empty cells are ignored!
    maxw=l>maxw?l:maxw;
    minw=l<minw?l:minw;
  }
  if( maxw==minw) { /* when largest width equals smallest width all are equal */
    var s = new Array();
    for( i=0 ; i<al ; i++){
      if( arguments[i].constructor == Array ) s = s.concat( arguments[i].slice() )
      else if (arguments[i].length!=0) s = s.concat( [[arguments[i]]] )  
    }
  if ( s.length == 0 ) return null ; else return s        //s     
  }
  else return "#N/A: All data ranges must be of equal width!"      
}

After copying the above script, lets now see the formulas:

Here is the screenshot of the Spreadsheet:

I have inserted the following formula in Cell R4, this formula will auto populate the Column R, S, T and U:

=arrayformula(if({{1},{0},{0},{0}}; if(J4:J="","",if(J4:J<L4:L,"0","1") ) ; if({{0},{1},{0},{0}}; if(L4:L="","",if(J4:J>L4:L,"0","1") ) ;if({{0},{0},{1},{0}}; if(N4:N="","",if(P4:P>N4:N,"0","1") ) ; if(P4:P="","",if(P4:P<N4:N,"0","1") ) ) ) ) )

And the following formula in Cell W4, this formula will auto populate the Column W, X and Y:

=ArrayFormula(IF({{1},{0},{0}};if(R4:R="";"";R4:R+T4:T);IF({{0},{1},{0}};if(R4:R="";"";"-");if(S4:S="";"";S4:S+U4:U))))

And the main formula is in the Cell AB4, which will automatically fill the whole range "AB4:AJ16":

=Query(ArrayFormula(If({{1},{0},{0},{0},{0},{0},{0},{0},{0}};VMerge(E4:E19;H4:H19;E4:E19;H4:H19);If({{0},{1},{0},{0},{0},{0},{0},{0},{0}};1;If({{0},{0},{1},{0},{0},{0},{0},{0},{0}};VMerge(E4:E19;H4:H19);If({{0},{0},{0},{1},{0},{0},{0},{0},{0}};IF(VMerge(W4:W19;Y4:Y19)>1;1;0);If({{0},{0},{0},{0},{1},{0},{0},{0},{0}};IF(VMerge(W4:W19;Y4:Y19)<1;1;0);If({{0},{0},{0},{0},{0},{1},{0},{0},{0}};IF(VMerge(W4:W19;Y4:Y19)=1;1;0);If({{0},{0},{0},{0},{0},{0},{1},{0},{0}};VMerge(J4:J19;L4:L19;N4:N19;P4:P19);If({{0},{0},{0},{0},{0},{0},{0},{1},{0}};VMerge(L4:L19;J4:J19;P4:P19;N4:N19);)))))))));"select Col1,(sum(Col2)/2),sum(Col2),sum(Col4), sum(Col5), sum(Col6), sum(Col7), sum(Col8), (sum(Col7)-sum(Col8)) group by Col1 order by sum(Col4) desc label Col1 '', (sum(Col2)/2) '', sum(Col2) '',sum(Col4) '', sum(Col5) '', sum(Col6) '', sum(Col7) '', sum(Col8) '', (sum(Col7)-sum(Col8)) '' ")

the above formula will use the VMerge script that you have inserted in script editor. 

If you want to have a look at the spreadsheet, then comment on this blog post regarding it and I will share this spreadsheet with you.



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 or confidential projects:

http://igoogledrive.blogspot.in/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 convert time in mm-ss

Question:


( by CC Coach )

I'm using Chrome on a Mac. 

I have a spreadsheet I'm using for all of my cross country athletes' data. I enter their race times and get their paces calculated. Now I'm trying to crunch some numbers but running into trouble. 

I'm working specifically with mile paces (everything is under 9:00/mile)

1. First of all, I wish I could use a m:ss format easily. But that format is not built-in right now. It's a little maddening, because it's easy to do in Excel and Numbers. I want paces to come out as 6:06. I really don't want 06:06 and definitely not 0:06:00 or 6:06:00. For now, I'm using a time format (15:59 choice) and it works. 

2. My biggest problem is I want to do some calculations and projections based on difficulty of course. I'm trying to take a bunch of paces for lots of runners and do things like add 8 seconds to get a projection for another course. I get a parse error if I do something like d6+:08. It will work if I format all cells as plain text and enter my times like 6.06 and then use my formula as d6+.08. But then I have to retype all the times I copied from my other sheets since they are all in 6:06 format. 

Any help is greatly appreciated.

Solution:

Screenshot of the Spreadsheet:

Format all your cells as Plain Text, in which you have put the values as mm:ss and then put the values. And also format the cells in which you want the results as mm:ss

Format your Cell D6 as Plain Text and then put in the value as mm:ss format.

Now, if your Cell D6 is having value 6:06

and you want to add 8 seconds to it,

then put the following formula in Cell D20:

=IF(MOD(QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8;60)>9;ROUNDDOWN((QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8)/60)&":"&MOD(QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8;60);ROUNDDOWN((QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8)/60)&":0"&MOD(QUERY(SPLIT(D6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")+8;60))

So now Cell D20 will have the result as 6:14

I have marked the +8 in yellow so that you can easily modify it when you need to add more or less than 8 seconds

Your Cell E6 is having value 5:47

And now if you want to subtract 8 seconds from Cell E6

then put the following formula in Cell E20

=IF(MOD(QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8;60)>9;ROUNDDOWN((QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8)/60)&":"&MOD(QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8;60);ROUNDDOWN((QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8)/60)&":0"&MOD(QUERY(SPLIT(E6;":");"select ((Col1*60)+(Col2)) label ((Col1*60)+(Col2)) '' ")-8;60))

So now Cell E20 will have the result as 5:39

And similarly you can manipulate any of the cell and convert it to mm:ss format.


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 or confidential projects:

http://igoogledrive.blogspot.in/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 Concatenating columns from query results

Question:

( by christopher.r.haley )

I sell items on eBay and log them in google spreadsheet.  I'd like to see which weekday is the best day for me in terms of when I make a sale.  In order to do this I have a column where I log the date a sale was made.  For a short example, assume this is my data (2nd column in the formulate below is necessary because you can't aggregate and non-aggregate on the same column in the SELECT query:

06-18-12

06-19-12

06-19-12

06-21-12

I then have a query to determine which day I sell the most on:

=ArrayFormula(Query((A:B),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 IS NOT NULL GROUP BY dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"))

This gives me the following:

DoW | Occurances

2 | 1

3 | 2

5 | 1

What I want to see is something like this:

DoW (Occurances)

2 (1)

3 (2)

5 (1)

Essentiall, I'd like to concatenate the columns from the query's results. Better yet I'd like the DoW to be the 3 letter representation for the weekday (i.e. 2 = "Mon", 3 = "Tue", 5 = "Thu"). I know I can do all of this with multiple rows/columns and either hide them or use another sheet. However, I would LOVE to do it all in one call, if possible.

Solution:


If you are having the dates in Column A:

6/18/12

6/19/12

6/19/12

6/21/12

Then put the following formula in some other column, like in Column B:

=arrayformula(concat(concat(concat(query(ArrayFormula(Query(IF({1,1};IF(A:A="";"";TEXT(A:A;"DDD"))),"SELECT Col1, COUNT(Col2) WHERE Col1 <> '' GROUP BY Col1 Label Col1 'DoW', COUNT(Col2) 'Occurances'"));"select Col1");" (");query(ArrayFormula(Query(IF({1,1};IF(A:A="";"";TEXT(A:A;"DDD"))),"SELECT Col1, COUNT(Col2) WHERE Col1 <> '' GROUP BY Col1 Label Col1 'DoW', COUNT(Col2) 'Occurances'"));"select Col2"));")"))

The output of the above formula would be:

DoW (Occurances)

Mon (1)

Thu (1)

Tue (2)

And if you want the output in Ascending order of days, that is Monday then Tuesday then Wednesday and so on... then try the following formula:

=arrayformula(concat(concat(concat(TEXT(query(ArrayFormula(query(ArrayFormula(Query(IF({1,1};A:A),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 is not null GROUP BY dayOfWeek(Col1) order by dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"));"select Col1"));"select Col1");"DDD");" (");query(ArrayFormula(Query(IF({1,1};A:A),"SELECT dayOfWeek(Col1), COUNT(Col2) WHERE Col1 is not null GROUP BY dayOfWeek(Col1) order by dayOfWeek(Col1) Label dayOfWeek(Col1) 'DoW', COUNT(Col2) 'Occurances'"));"select Col2"));")"))

The output of the above formula would be:

DoW (Occurances)

Mon (1)

Tue (2)

Thu (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 or confidential projects:

http://igoogledrive.blogspot.in/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 track YouTube views

Question:

( by DigitalSauce )

Hello, Currently I am helping a friend track his YouTube views using a google doc. I want to automate this process by having a script grab this line of code:

<td colspan="2" bgcolor="#cccccc"><font color="green">+4,677</font><br><font color="green">+140,303</font> <br></td>

This line of code displays how many views a day he is getting(4,677) per the site: http://socialblade.com/youtube/user/morninafterkill. This line of code also displays the views gotten per month (140,303) How ever I need the script to be able to just grab the socialblade URL from the cell next to it, so in turn I can track multiple channels on the same forum. I would like for it to update its self daily.Can anyone help me with this?

More info: 
Url will be in cell: G*
Views a day in cell: H*
Views a Month in cell: I*
(*any number)

Solution:

Put the following link (URL) in the Cell G2:

http://socialblade.com/youtube/user/morninafterkill

Put the following formula in the cell where you want to get the (average of last 30 days) Views:

=Query(SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10));"select Col1")

Put the following formula in the cell where you want to get the (average) views for month:

=Query(SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10));"select Col2")

And if you wish to get the "Views" and "Views a Month" in adjacent cells then you can get both values using the following single formula:

=SPLIT(Query(transpose(ImportHtml(G2;"table";0));"select Col34 where Col2 = 'Video Views'");Char(10))

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 or confidential projects:

http://igoogledrive.blogspot.in/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