Wednesday, May 30, 2012

yogi_Rearrange Columns Of Response Table In Specified Order Based On Specified Number Of Beginning AlphaNumeric Characters

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #569    May 30, 2012     www.energyefficientbuild.com.


user Robby B said:
frequent reordering of column responses in forms spreadsheet
Firefox
windows 7
Others have reported the issue that the order in which questions are created in google forms is the order in which output is displayed in the spreadsheet regardless of how one changes the order in the form (which is often necessary). In my job I am creating new forms fairly often and they get changed (by myself and others) so keeping the questions in created order is rather difficult. 
I am looking for a function that can reorder the columns in a new tab/sheet. It would be helpful if my labeling scheme, (1a,1b,1c,2a,2b,2c, etc) which is at the start of each question, can be used in the identification (because this scheme is the only thing that will remain the same across all the forms I create). 
Any suggests?
-R

-----------------------------------------------------------------------------------------
I have used helper Sheet1a to do the prep work for finally rearranging the columns of the Response Table in Sheet1. I have generalized the solution by using the number of beginning characters of the question headers as a variable and also having the option of rearranging the questions in Ascending or Descending order.


Monday, May 28, 2012

yogi_Create A League Scoring Table Showing Ranks As 1st 2nd 3rd And So On

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #568    May 28, 2012     www.energyefficientbuild.com.


user Gary Colin said:
Problems creating a simple league table 
....
This simpler formula makes rest of my tasks a lot easier I think.
But I've another question Yogi... 
I had a very long-winded way of adding 1st, 2nd, 3rd etc as the first column in the league table.
So cells I3, I4, I6, etc on your table would become "1st", "2nd", "3rd" rather than just "1", "2", "3".
Any ideas?
Gary

-----------------------------------------------------------------------------------------
following is a solution to the problem ... my solution uses a single formula for the solution -- alright the formula is long but it is pretty straight forward solution. Perhaps a separate post on creating 1st 2nd 3rd etc. would be in order because that has application for many different types of problem ... but let us leave it to another time.

Sunday, May 27, 2012

yogi_Setup Computed Column Formulas For Quantities And Prices As Specified

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #567    May 27, 2012     www.energyefficientbuild.com.

I have posted the following solution to the problem based on a question by user AWark ...
http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/5hVt7a1FIY0

The formulas for computed columns need to be array formula in row1 of the computed column for the formulas to work correctly and get automatically populated as the submittals get logged in the Form responses sheet

yogi_Join Non-Blank Entries In A Given Range Of Columns And Transpose To Corresponding Rows Per Specification

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #566    May 27, 2012     www.energyefficientbuild.com.

user TomSleekUK said:
Simplify this long formula?
C17:U34 - Is where the data is
V5:V11 - Is where I want the result
Currently I am using a really long formula that leaves trailing commas, but thats not really important.
If C17>0 then join C17 to U17, if not then do nothing.
Then do the same to the next row (C18) and join it on to the first result
In this case U17 and U18 are 0 so they should be ignored
I want a formula that does this for each row and joins the results together e.g.
1 Tuna, 1 Brown rice, 1 Banana
If U17 was 1 then it would be
1 Chicken, 1 Tuna, 1 Brown rice, 1 Banana
It would be easier to work on the sheet together so I can point and live explain what I mean; it's hard doing this not in real time.
Thanks in advance

---------------------------------------------------------------
following is a solution ... in regard to the needed formula in cells V5:V11

yogi_Expand An Item Count Into Corresponding Number Of Rows For Two Columns

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #565    May 27, 2012     www.energyefficientbuild.com.

this is an extension of the problem for which I had posted a solution in my following blog post:
http://yogi--anand-consulting.blogspot.com/2012/05/yogiexpand-item-count-into.html
-----
as an extension of the earler problem, user fearless said 
Okay, I have a solution that uses VLOOKUP in what I feel is a much cleaner approach.  I've posted my solution in:
The gist of it is that I use VLOOKUP as follows:
 =if(C23)>=$C$19,"",VLOOKUP(C23;$C$12:$E$18;2;true))
... where the first row of the array (C12, C13, ....) contains the running sum of the quantities so far. Take a look - it's relatively simple.
- ff
------------------------------------------------------------------------------------------------------
Great Job by user fearless.
There is generally more than one approach to a problem ... which solution is better is a mute point, because a solution has to fit the user's comfort level, project preferences, project constraints and so on. Also as situation evolves, one may begin to prefer another solution compared to the one one had chosen to use in a different situation, under different circumstances.

In any event, following is my single array formula without using any helper columns, or multiple helper formulas ... different YES! ... convoluted YES! ... better ... Don't Ask Me! ... it all depends on your situation.


yogi_Compute Sum Of Entries In A Column For The Last Specified Number Of Days

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #564    May 27, 2012     www.energyefficientbuild.com.

Based on a post by Adam ...
http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/LEYuTOmGLhw
I have created the following illustration

Saturday, May 26, 2012

yogi_Expand An Item Count Into Corresponding Number Of Rows

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #563    May 26, 2012     www.energyefficientbuild.com.

user fearless said:
expand an item count into a number of rows
[Using Chrome/OS X, but that's probably not relevant.]
Consider the following on one sheet:
quantity, description, accumulated
0, pony, 0
4, lollipop, 4
2, rainbow, 6
1, fairy, 7
On another sheet, I'd like that to expand to be:
item, description
1, lollipop
2, lollipop
3, lollipop
4, lollipop
5, rainbow
6, rainbow
7, fairy
that is: I get <quantity> rows of each item.  I can do this with a nested IF statement, but there's a nested IF for each item, so it gets unwieldy very quickly.  I'd prefer NOT to resort to scripting.  VLOOKUP holds promise, but I haven't been able to make it jump through this particular hoop.
Any suggestions?
-------------------------------------------------------------------------------------------------
following is a solution to the problem ... for clarity I have posted the results in the same sheet ... posting the results in another sheet is not a problem


yogi_Select The First Blank And First D In Any Column Of Given Table And Post Values In Column A Of Corresponding Rows

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #562    May 26, 2012     www.energyefficientbuild.com.

user Chewy3399 said:
Selecting the last data
I have a spreadsheet that a friend and I both use a form to enter data in. One column either has a value of blank, for my data, or a D for his data, what function could I use to retrieve the last entry for each of us, it can be in two separate cells.
----

Is a sample of my data, basically what I want to do is extract the time column for the last entry for D and for "Blank" and put that time stamp in another cell.
--------------------------------------------------------------------------------------------------------------------
following is my proposed solution to the problem


yogi_Compute Average Of A Growing Set of Numbers In A Range Row By Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #561    May 26, 2012     www.energyefficientbuild.com.

user Trillium said:
Infinite average
I am trying to create a total percentage using averages. however i do not want to continue changing the formula to coincide with the number of entries. i want the formula to add all of the entries as they are added. for example lets say i have 5 entries. i have (1+1+0+1+0)/5 to give .60 or 60%. yet if i added a cell it would be (1+1+0+1+0+1)/5 instead of being divided by 6. how do i get the formula to recognize the added cell and account for it in the division of total numbers. also how do i get the formula to ignore blank cells in the total number of cells
------------------------------------------------------------------------------------------------------
following is a solution to the problem

Friday, May 25, 2012

yogi_Create A Timestamp Column If The Entries In A Column To Its Right Are Changed

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #560    May 25, 2012     www.energyefficientbuild.com.


user sally Sally said:
Timestamps
How do you create a time stamp column in a new sheet of an existing spreadsheet?  I want it to time stamp each time the column to the right is edited.
Thanks!
-----------------------------------------------------------------------------------------------------

couple of thoughts ...

1) you can explore using a Google Apps Script for this -- that would be pretty straight forward

2) a formula based approach as presented in the following  at best is a convoluted workaround 

yogi_Sum Up Sheet1_B:D In Sheet2 And Even If A Column Is Inserted In Sheet1 Sheet2 Should Still Show Sum Of Sheet1_B:D

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #559    May 25, 2012     www.energyefficientbuild.com.

user murality123 said:
Sum - issues
using google docs, want to do a sum from other spread sheet, but when i insert a column the values are changing.
Example: 
1] Sheet1 has numbers in coulmn B, C, & D
2] Sheet2 have done sum of Sheet1's B C & D
3] If i Insert a Column in Sheet1 B... total in the sheet2 is showing a sum of column C, D & E.
Would like to know how do i get the values of only colum B C & D's values in the other spread sheet... even if i insert columns in Sheet1 - sum value should change only if the value changes in column B,C, & D
-----------------------------------------------------------------------------------------------------
following is a solution to the problem


Thursday, May 24, 2012

yogi_Autonumber Lines Within Cells Containing Text Row By Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #558    May 24, 2012     www.energyefficientbuild.com.

user barrybaker said:
Is there a way of auto-numbering lines WITHIN a cell? 
Is there a way of auto-numbering, or bullet pointing lines within a cell?
  1. Like this
  2. and this
Like this:










--------------------------------------------------------------
there is no direct way to acomplish what the OP wants ... 
in the following I present a workaround for this

yogi_Count Instances Of An Entity Or A Combination Of Specified Entities In A Column

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #557    May 24, 2012     www.energyefficientbuild.com.

user jmcddonal said:
Looking for Google Doc equivelent formula to COUNTIFS
Can someone help me with this? I'm not really very good with Excel and I need to create a Google spreadsheet that I would normally use a COUNTIFS statement.
Have a spreadsheet has a list of computer laptop hostnames where the first three letters of the hostname determine whether it is a PC assigned to a Faculty member, or non-Faculty admin staff, with a total for each.
Here's a sample of the spreadsheet data I am working from
e1 Hostnames:
e2 LTFAAA
e3 LTDBBB
e4 LTDCCC
e5 LTDDDD
e6 LTDEEE
e7 LTDEEE
e8 LTSFFF
e9 LTSGGG
e10 LTCHHH
Total Faculty PCs (LTF*) - This works OK: =COUNTIF(E2:E10,"LTF*")
Total Admin PCs (LTD* and LTS*) THIS IS THE ONE I CAN'T FIGURE OUT
Total Academic PCs (LTC*) - This works OK: =COUNTIF(E2:E10,"LTF*")
------------------------------------------------------------------------------------------------------------
following is a solution to the problem


yogi_Extract From Sheet1 Into Sheet2 Rows As Specified In Sheet3

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #556    May 23, 2012     www.energyefficientbuild.com.

user RoberA said:
delete a lot of rows by marking
the problem is, that i have a big document where is only every second row are used. now i mark every single row, right-click, 'delete row'. is there a possibilty to mark a lot of rows (every second) together to make it faster?
----------------------------------------------------------------------------------------
in the following generalized proposed solution to the problem, instead of deleting the rows in Sheet1 I have extracted in Sheet2 rows of Sheet1 as specified in Sheet3

Wednesday, May 23, 2012

yogi_Set Up A Computed Column For AutoNumbering XandRowNumber In The Form Responses Sheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #555    May 23, 2012     www.energyefficientbuild.com.

MALuisa.Sanchez said:
How to autonumber a column only if row has data on it? (Data came from a form)
Hi everyone ..
We are trying to experiment on google docs before we buy google aps for our business.
This is a link of a spreadsheet we are trying to come up with
https://docs.google.com/spreadsheet/ccc?key=0AryiH8y2wxZtdHhaeXJhR1dFMkJpaDB5LVpzVDBGcEE
 Spreadsheet is filled using a form.. I need to autonumber the rows in order to keep track of items added.  There is already a formula on Column B, but I would like it to NOT AUTONUMBER if the row has not yet been added (or filled)...
Can you make it happen?? pleaseeeeeeeeeeeeeeeeeeeeeeeee
-------------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Setup Table To Enter Start LunchOut LunchIn End Hours In 12 Hour HH:MM Format And Compute Hours Worked In HH:MM

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #554    May 23, 2012     www.energyefficientbuild.com.

user  Sparky Cider said:
Spreadsheet: Tracking working hours (minus the lunch breaks). Also changing the format from 24-hour to 12-hour
Hello,
I currently have a spreadsheet that we are going to use for our business. To give you an idea of our spreadsheet, it has the following fields:

Start (B12)
Lunch Out (C12)
Lunch In (D12)
End (E12)
Now, the employee does not get paid for their lunch break, so we need to minus that from their daily working hours. I would like to use the 12-hour format instead of the 24-hour format. I did see examples how to use the 24-hour format, some that worked -- some that do not work. Also, is it possible to change the input format in spreadsheets? I am trying to locate it but I am having a hard time finding it. For example, you can change this in MS Office, so I am not sure if Google has this capability yet.
For example: If a user inputs 8:00, it automatically changes it to: 8:00:00. Now, I can tell you this will be confusing to some employees, so I would like to dismiss that :00. I would also like to know if we could dismiss military time because employees will most likely not favor this new-improved spreadsheet.
Please advise :-)
------------------------------------------------
following is a solution to the problem



yogi_Concatenate Data From Various Columns Row By Row Per Specification

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #553    May 23, 2012     www.energyefficientbuild.com.

use tabus said:
arrayformula causing trouble 
Hi there,
I've got this problem:
The spreadsheet contains 4 columns where dates (formatted as text, but it must be so) are inserted and the 5th column, where as a result I have the input from columns 1-4, joined together but separated with ";". So it looks like this:
column A       column B     column C     column D                   column E (result)
2012.02.01   2012.02.03   2012.03.01   2012.03.12   2012.02.01;2012.02.03;2012.03.01;2012.03.12
However, the condition is that each of the dates in the result column can be followed by the separator ";" only, when following cell is neither empty nor filled with "nd" text. 
In other words the condition is met only when following cell (within 1-4 column) is filled with the date. So, if only the columns A-B were properly completed, the table would look like this:
column A       column B     column C     column D                   column E (result)
2012.02.01   2012.02.03                                                  2012.02.01;2012.02.03
or:
column A       column B     column C     column D                   column E (result)
2012.02.01         nd                nd               nd                       2012.02.01
Now, the formula I used is as follows:
=if(and(A2<>"";B2<>"nd";B2<>"");A2&";";if(A2<>"";A2;""))&if(and(B2<>"";C2<>"nd";C2<>"");B2&";";if(and(B2<>"";B2<>"nd");B2;""))&if(and(C2<>"";D2<>"nd";D2<>"");C2&";";if(and(C2<>"";C2<>"nd");C2;""))&if(D2<>"nd";D2;"") 
and it works fine, UNTIL "ARRAYFORMULA" IS USED AT THE BEGINNING
=arrayformula(if(and(A2:A<>"";B2:B<>"nd";B2:B<>"");A2:A&";";if(A2:A<>"";A2:A;""))&if(and(B2:B<>"";C2:C<>"nd";C2:C<>"");B2:B&";";if(and(B2:B<>"";B2:B<>"nd");B2:B;""))&if(and(C2:C<>"";D2:D<>"nd";D2:D<>"");C2:C&";";if(and(C2:C<>"";C2:C<>"nd");C2:C;""))&if(D2:D<>"nd";D2:D;""))
Then the table looks like this:
column A       column B     column C     column D                   column E (result)
2012.02.01   2012.02.03   2012.03.01   2012.03.12              2012.02.012012.03.12
So, columns A and D are joined together but without the separator, and columns B-C vanish entirely!
Any ideas what I'm doing wrong and how to fix it?
Thanks in advance:)
Browser - Chrome
OS - Win7 HP x64
-------------------------------------------------------------------------------------------------
following is a solution to the problem


Tuesday, May 22, 2012

yogi_Compute The Number Of Non-Blank Rows In Specified Columns

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #553    May 22, 2012     www.energyefficientbuild.com.

user NYVergara said:
Is it possible to count the number of rows in a given field with only names in the cells? (no numbers) I want a total of cells which have data not empty cells
I have a spreadsheet and would like to count the number of rows used. There are several columns but not all of the rows have data. Example: Column D: I am using row 31 to 50 in Column E: I am using Row 31 to 43 can I get a total in row 51 that tells me how many rows were used in column B  and how many rows were used in column E? Thanks!
----------------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Arrange Address Labels Of Specified Number Of Rows Per Label In One Record Per Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #552    May 22, 2012     www.energyefficientbuild.com.

user ginanet812 said:
I want text pasted in a column to actually go across several columns in one row- each line pasting into a new field/cell
I am copying and pasting addresses that are in this format . Is there a way to get the info to spread across columns in one row - each line being the next field when I paste? I have searched help but can't find any answers. Maybe I am using the wrong terminology. 
copying:
Company
17075 Smith st
Big City
CA
92000
Want it to go across in separate cells so I can make labels or sort by field:
Company I 17075 Smith st I Big City I CA I 92000

--------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Workaround For Applying Conditional Formatting When A Cell Value Needs To Be Compared With Value In Another Cell

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #551    May 22, 2012     www.energyefficientbuild.com.

user Scott DK said:
Can you do IF THEN formulas in Google spreadsheets?
I'm trying to compare cell A1 with cell A2.  If A1 is greater it turns red.  If A2 is greater it turns green.  Is this function possible in Google Docs?
-----
The conditional formatting is sort of what I'm looking for but I want it to compare the value of the previous cell.  It looks to me as if I can only compare to a predetermined number that I set.  Is that true?
---------------------------------------------------------------------------------------------

in Google spreadsheet as of now (May-2012) anyway Conditional Formatting can not be applied to a cell by comparing its value to another cell ... the cell value can be compared with a known (predetermined) value only.


So, as a workaround, I can use an adjacent column, saycolumn B wherein I can compare the value of the cells of interest and then apply Conditional Formatting to cells of corresponding rows in column B


as presented in the following solution to the problem