Google Spreadsheet Calculating Rank of Players

Question:
(by Thomas)

Hello at all!

First, i have to say that my english is really not the best, but i hope you can understand and help me.

I have a Spreadsheet with some friends.
We play a racing game and want to check our times against each other.
I want to compare our times with the RANK-function, BUT there shouldn't be the ranking like "1,2,3,4,..." - it should be 10 points for 1st place, 8 points for 2nd place, 6 points for 3rd place and so on.
I have a problem with the RANK because it doesn't compare my times.

For example:
Player 1 - A1 = 1:04,75
Player 2 - B1 = 1:03,98
Player 3 - C1 = 1:05,60
Player 4 - D1 = 0:59,63

The result of the RANK-function should be:
A2 = 6 (points)
B2 = 8 (points)
C2 = 5 (points)
D2 = 10 (points)

I can't say that die format from A1 to D1 should be mm:ss,00 (minutes:seconds,hundredth of a second).
How can i solve the problem that i can compare our times?

Kind regards from Germany

Solution:

Have a look at the screenshot below:

Now in the above screenshot I have marked green color to the cells which contains formulas.

Your final required formula is in Cell A2 and that is:
=transpose(query(arrayformula(if({1,0};query(arrayformula(if({1,0,0};{"A";"B";"C";"D"};if({0,1,0};transpose((arrayformula(iferror(int(left(A1:D1;len(A1:D1)-6)))*1)));transpose((arrayformula(iferror(int(left(right(A1:D1;5);2)&right(A1:D1;2)))*1))))));"select Col1 order by Col2,Col3");{10;8;6;4}));"select Col2 order by Col1"))

And as the above formula is very much complex, you can understand it with the help of following formulas:

I have following formula in Cell A4:
=transpose((arrayformula(iferror(int(left(A1:D1;len(A1:D1)-6)))*1)))

I have following formula in Cell B4:

=transpose((arrayformula(iferror(int(left(right(A1:D1;5);2)&right(A1:D1;2)))*1)))

I have following formula in Cell A4:

=arrayformula(if({1,0,0};{"A";"B";"C";"D"};if({0,1,0};transpose((arrayformula(iferror(int(left(A1:D1;len(A1:D1)-6)))*1)));transpose((arrayformula(iferror(int(left(right(A1:D1;5);2)&right(A1:D1;2)))*1))))))

Note: Above formula will work for only cells A1:D1. I mean it will show ranks only for four players. But you can work out on this formula to have formula for more or less players.

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 Advanced Query

Question:

I am trying to figure out a solution for a particular problem. I am using a calculation posted on this forum to count only once for multiple same date entries, however I also want these counted by a value in another column. For example, If there are multiple entries for the same date, but the column next to one entry is "blue" and another entry for the same date is "red", each of these is given its own count. I used the formula from this forum:
=Query(index(if({1,1},int('Sheet1'!A:A))),"select Col1,count(Col2) where Col1>1899 group by Col1 ",0)
To count the total number of unique days, but tried to modify it to be able to determine how many days I worked on "blue" or "red" regardless of the number of entries for the same day. I can share the spreadsheet I am working on if it would be helpful.

====


I have tried a number of possible solutions, but am unable to get this to work. Any help would be greatly appreciated.

I tried to modify the equation to only return those days that I had entered work for a specific project work-type. For example, if I create multiple entries on the same date for "Technology Rework" on the first page, I want to show that only one day was spent working on that portion. If I include another date entry for "Technology Rework" it would calculate the total number of days worked as 2 days. So I don't want to count more than once for a single date, but I want that time to be added to the number of total days worked on that area of the project.
I have included sample data in the following link:



The equation is in field I6 on sheets "Chat Page Rework" and "Technology Rework"

Please let me know if there is any further information I can provide.

Thank you for your help,
Kyle

Solution:

Try the following formulas one by one:

=query(Sheet1!A4:C;"select C,A,sum(B) where C='Chat Page Rework' group by C,A label A '', sum(B) ''";0)

=query(query(Sheet1!A4:C;"select C,A,sum(B) where C='Chat Page Rework' group by C,A label A '', sum(B) ''";0);"select Col1,count(Col2),sum(Col3) group by Col1 label Col1 '',count(Col2) '',sum(Col3) ''")

=query(query(query(Sheet1!A4:C;"select C,A,sum(B) where C='Chat Page Rework' group by C,A label A '', sum(B) ''";0);"select Col1,count(Col2),sum(Col3) group by Col1 label Col1 '',count(Col2) '',sum(Col3) ''");"select Col2,Col3")

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 SUMIF multiple criteria using FILTER

Question:

Hi, 

I am having a bit of trouble coming up with the right formula, mostly because of my ignorance in advanced formula, but also because of the difficulty of the task.

A basic break down of what I am looking for is this

If A3:A99 = N3 or N4 AND C3:C99 = N15 Then Count E3:E99

I was able to get close using this

=arrayformula(SUM((A3:A99=N3)*(C3:C99=N15))) .... But all this does is count how many times it occurs .. not E3:E99 which is what I really need.

Thanks for any help

Note: that formula also doesnt give me N4 .. just N3

Im sorry my example was very clear .. lets chalk that up to my ignorance. I will try again. First off, Here is a copy of the spreadsheet:

What I am trying to create is a formula that will add up the values between E3:E99 when these conditions are met:

A3:A99 = "AHL ECU" OR "AHL" and C3:C99 = "CON"

Solution:

Try the following formula:

=sum(iferror(filter(E3:E99;(A3:A99 = "AHL ECU")+(A3:A99 = "AHL");C3:C99 = "CON")))

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 to calculate remaining working days in a month

Number of days left in a month, excluding weekend (Saturdays and Sundays)

Question:

Based on today's date, I'm trying to determine what formula to use to tell me how many days are left in a month, excluding weekends. 

I basically want the spreadsheet to take today's date and give me the number of days excluding weekends. 

Thanks for any help. I've been looking around for a little while today and can't come up with anything. 

Solution:

When you are trying to calculate how many days you have left before reaching a project deadline, try the following formula of Spreadsheet.

You don’t need to count out days on a calendar to find out how many days you have to complete a project. Google Spreadsheet’s NETWORKDAYS function can calculate it for you — without requiring you to work weekends or holidays.

Try the following formula:

=NETWORKDAYS(TODAY();EOMONTH(TODAY();0))

Explaining NETWORKDAYS:

NETWORKDAYS(start_date, end_date, holidays)

Returns the number of workdays between start_date and end_date. Holidays can be deducted. Start_date is the date from which the calculation is carried out. End_date is the date up to which the calculation is carried out. If the start or end date is a workday, the day is included in the calculation. Holidays (optional) is a list of holidays. Enter a cell range in which the holidays are listed individually.

Explaining EOMONTH:

EOMONTH(start_date, months)

Returns the date of the last day of a month which falls Months away from the given Start_date. Months is the number of months before (negative) or after (positive) the start date.

Google Spreadsheet Script How To Auto Rename Sheet

Question:

I have a sheet consisting of a master sheet and some other sheets. My master sheet is connected to the others so that I can retrieve the information of the sheets in my master.

Here is my question:

Whenever I add a row to my master sheet I want two things to happen:

1- it adds a new sheet calling it QC26 (for example if the latest sheet is QC25 I want that it names this sheet as QC26 and the next one QC27 and so on...) 

2-Copy the formula in the above row to this row considering that the information from the above row comes from sheet QC25 and the one for this example has to be the same cells but from sheet QC26 and so on.

I already know how to duplicate the sheet with the following script but I cannot change its name automatically.

function create()

{

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var temp = ss.getSheetByName("Template");

  ss.insertSheet("QC2", 1, {template:temp});

}

Can anyone help me with this?

Solution:

Try the following Script:

function create()

{

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var temp = ss.getSheetByName("Template");

  

  var sheets =  ss.getSheets();

  var sheetname = sheets[sheets.length-1].getSheetName();

  

  var oldname = String(sheetname).substring(2,4);

  var temp_increment = parseInt(oldname)+1;

  var newname = String(sheetname).substring(0,2)+temp_increment;

  //Browser.msgBox(newname);

  ss.insertSheet(newname, sheets.length, {template:temp});

}

Note:

You must have latest Sheet for example "QC30" at the right most side, that is at the right end side of the sheets panel.

The above script will take the name of the last sheet and then it will take the substring of the name and then rename it and insert the new sheet at the last position.

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 Query BY keyword

Question:

Hello,

I am having issues with referencing a particular column in a query function.

The project I am working on involves the creation of a room audit tool for easy data collection.  These rooms are grouped into different zones and different people are responsible for different zones.

Right now I have a spreadsheet set up so that form responses are automatically separated based off of zones by using the query function.

This form creates many columns in the responses and when I try to reference column BY in Sheet 'Form Responses' in order to put into the sheet 'Zone B,' the query function breaks because 'by' is a word and I can't get it to read the column in any other way.

Here is a link to the screenshot of my spreadsheet:

Here is the formula I was using in cell A1 of sheet Zone B: =sort(query('Form Responses'!A1:FO,"select A,B,AT,AU,AV,AW,AX,AY,AZ,BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BZ,CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO where C='B'",1),1,false)

I need to be able to select by zone and I need to be able to have it sort so that it can have the most recent submissions remain at the top.

Thank you for your time.

Solution:

'BY' is the keyword of query formula so it is having this issue...

You can try:

=sort(query(ArrayFormula('Form Responses'!A1:FO),"select Col1,Col2,Col46,Col47 where Col3='B'",1),1,false)

Here in the above query you have to use formula Arrayformula in your query's data and then instead of refering columns by A,B,C.. you can refer them by Col1,Col2,Col3 and so on...

I hope this helps you..

Google Spreadsheet Synchronize Two Sheets

Google Spreadsheet Synchronize Two Sheets

Question:

(by Omar)

I have 2 worksheets. Sheet1 and Sheet2

I want to have cell A1 on Sheet1 to be shown on Sheet2 - I can do this

BUT... what I want is to be able to change the value in both places - a change made on Sheet 2, gets shown on Sheet 1

And a change made on Sheet 1 shoes on Sheet 2

How can I do?

Thanks

Solution:

Now, have a look at the following script:

function onEdit()

{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var activeSheetName = ss.getActiveSheet().getSheetName();
  
  if( activeSheetName == "Sheet1" || activeSheetName == "Sheet2" )
  {
    var activeCell = activeSheet.getActiveCell();
    var activeCellinA1 = activeCell.getA1Notation();
    
    if( activeCellinA1 == "A1" )
    {
      var activeCellValue = activeCell.getValue();
      if( activeSheetName == "Sheet1" )
        ss.getSheetByName("Sheet2").getRange("A1").setValue(activeCellValue);
      if( activeSheetName == "Sheet2" )
        ss.getSheetByName("Sheet1").getRange("A1").setValue(activeCellValue);
    }      
  }
}



When you edit Cell A1 in either Sheet1 or Sheet2 then the above script will run the onEdit function, and Cell A1 on both the sheets that is Sheet1 and Sheet2 will automatically synchronize.


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