yogi_InsertHH:MM:SSforCurrentTimeAtTimesAndHold

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

glrcbs said ...

I need a shortcut to insert the current time of day in hours, minutes, and seconds, into a spreadsheet cell, preferably in HH:MM:SS format and need to be able to do that multiple times in a spreadsheet

I've tried  =NOW()

which correctly inserts the current time (and date) but whenever I add a new time, all those cells are updated to the newest time

I've tried ctl+:  

but it puts zeroes where the seconds should be

And I've tried     

=mid(index(ImportHtml("http://www.time.gov/timezone.cgi?Eastern/d/-5";"table";3);2;1);2;9)

which works the first time I use it in a spreadsheet. Every time I use it after that, it enters the same time as the first one.

Any suggestion?

-----------------------------------------------------------------------------------

As you have already noticed Ctrl+: or Ctrl-Shift-; as Gill suggested embed the current time in hh:mm:00 (with seconds as 0)

I have the following workaround for you using Data Validation from a List ... 

the List consists of one cell with the following formula ...

=mod(now(),1)

Check it out and see if this would work for you!

yogi_GroupOfCellsToChangeColorWhenAnotherCellHasX

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

LADTHOMPSON said ...

I am trying to format a group of cells to change when another cell has an "X" input to it. 
How can I do this in google?

This works in excel through conditional formatting.  However, google does not have conditional formatting or I am not doing something right.  For example.  Cells "A2" through "G2" (all cells in the same row) have different text entered ranging from names to numbers.  What I want to have happen is when cell "H2" has an "X" input, I want those previous cells to either have the information formatted to "Strikethrough" or become a lighter color of text.

----------------------------------------------------------------------------------------------------------

The following workaround using Change color with rules in an adjacent row, say row 3 
might be of some interest to you. So here we go

yogi_Count Comma Separated Values From Cell With Multiple Conditions

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Jogi said ...

How can i count comma separated values from a cell with multiple conditions?

My sheet looks like this:

Keywords                    Categories
aeroplane, plane, car       category 1
car, bicycle, plane         category 2
plane                       category 2

Desired output in second sheet:

Keywords    Category 1    Category 2    Sum
car             1             1          2
bicycle         0             1          1
aeroplane       1             0          1
plane           1             2          3

--------------------------------------------------

In my proposed solution, I first computed the unique items and then I computed the number of times the item appears in each category ... so here we go

Jogi commented:


These (formulas in Sheet1) are quite sophisticated formulas :) Frankly, I was looking for a simpler solution, and not so automatic. I don't want to count every single keyword, just some chosen few. So, let's say this is my desired output:


    A        B          C       D
Keywords Category_1 Category_2 Sum
car          1          1       2
bicycle      0          1       1
aeroplane    1          0       1
plane        1          2       3


I am  looking for a formula I can use for each cell separately. For example, in B2: count how many times "car" can be found in column A of Sheet 1 where the side cell from column B = "category 1".
------------------------------------------------------------------


In response to comment from Jogi, I have added Sheet2 in this post ... where formula in cell B2 is ...

=ArrayFormula(sumproduct(iferror(sign(search($A2,'Sheet1'!$A$2:$A))),
('Sheet1'!$B$2:$B=B$1)))

yogi_CountResponsesBetweenCertainRangeOfNumbers

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

DanM77 said ...
I have a column on a spreadsheet which shows the responses from a form. The responses are a number between 1 & 99.
I want to show a graph or the responses but in order to do so, I think I really need to group the responses into ranges (probably seperating it into 10 ranges of around 10 numbers). I was thinking a pie chart might be best but the problem that I'm having really is getting a count of numbers between ranges.
----------------------------------------------------------------------
Well, I have setup ranges of upto 10 numbers from 1 to 10, 11 to 20, 21 to 30, 31 to 40, and so on as far as we want to go within the limit of number of columns in the spreadsheet. So here we go ...

yogi_Form CheckBox Questions Split Into Own Columns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

emitsignal said ...

I am collecting registration details from schools participating in my charitable programme so I can match them. They're asked to select as many options as apply to them, but the results end up aggregated into single cells. Although these are comma delimited so I can split them in Excel, they end up spread across different columns which makes it impossible to sort meaningfully so I can match the schools properly. 

---------------------------------------------------------------------

I have assumed there are 5 CheckBoxes for each question

I have used the following layout from Andre (ahab)'s fine solution posted to emitsignal's question in Google Docs Help Forum. I had a little play with it and I present my following solution:

-----------------------------------------------------------------------------
update May-23-2011
I have added a scoring scheme as shown in sheet named TestScoresForYandNresponses
whrere both Y and N responses are assigned a score as shown in cells C4 and C6
and Test Scores for submittals are shown in column I

yogi_FormForWeekDayMealsLunchOrDinner

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Please view the spreadsheet above for open slots ... and then if you are interested in hosting Lunch or Dinner for a given day or days, fill in the FORM below



and SUBMIT. Your Form submittal will update the spreadsheet as presented in this post (you will have to refresh /reload the page on the browser to see the update in the spreadsheet.

---------------------------------------------------------

The following is a graphic representation of which slots for hosting Lunch, or Dinner for Linda have already been taken up ...

yogi_PlaceFormDataInSpecificLocation

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

mjbutterfield said ...
I am trying to set up a way for subcontractors to submit bids via a form and have them populate an estimate.  Here are the couple challenges I am trying to overcome.

Here is a simplified version:

Let's say I have 3 categories- foundation, framing, and sheetrock.  I need to look at the form responses and determine which category the response is supposed to be in.  I then need to place that amount in the spreadsheet for that category.

The second issue is that I would often like to have more than one bid in each category so I need to somehow have the data be placed in the next cell to the right if there is already another bid populated for that category.  I understand a lot of the functions within spreadsheets but could use some advice on the smartest way to accomplish this.  Below is a simplified example of the end product I am looking for:

Category         Vendor 1   Bid 1    Vendor 2  Bid 2     Vendor 3   Bid 3

_______________________________________________________________

Foundation      Joe         1000        Mike        1200

Framing           Jim         2000

Sheetrock        Tim         1000        Bill           900       Frank       1100
--------------------------------------------------------------------------------------------
In Sheet1, I have proposed a solution using the QUERY function ... the resulting layout in Sheet1 does not quite match the layout as requied by mjbutterfield. If the resulting layout in Sheet1 would do, we are all done.

However, to meet mjbutterfield's requirement, I present a solution in Sheet2 where I build up on the work done in Sheet1
Sheet3 however has a more straightforward solution using primarily the FILTER function along with the CONCATENATE and the SPLIT functiuons.
In Sheet4, I provide another QUERY formula based solution which is like the TRANSPOSE of the solution in Sheet1.

yogi_ShowOnlyOneIncidentOfProductID

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

mandi125 said ...

I have  A, B, C, D, E, F, etc as column PROD ID on one column .These products also have regions and markets where the products will be sold to. So those are represented as REGION and MARKET columns. Then I added a unique ID for each unique PROD+REGION+MARKET. So my question is how do I hide those multiple rows with the same PROD ID and show only one instance of PROD ID. And can unhide those rows when I want to edit them. So basically show only one instance of a PROD_ID no matter if column REGION and MARKET are duplicated or unique.

UNIQUE_ID   PROD_ID    REGION     MARKET

1                        514             X                A

2                        514             Y                A

3                        246             W               B 

4                        246             W               C 

5                        246              Z               B

6                        756             W               C 

7                        756              Z               A 

8                        756              Z               B

9                        846              Z               C 

10                      857              X               D

11                      999              X               D

12                      999              Y               D

--------------------------------------------------------------------
Well, I have a formula based solution that extracts the rows of the table only for unique values of Product_ID. mandi125 check it out and see if this will work for you

yogi_ConvertTelephoneNumbersFromDifferentFormats

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

fanny.o said ...

I have a column with tel numbers but they are in different formats.
Tel
99.99.99.99.99
99 99 99 99 99
9999999999

and i want to have the tel numbers always in the following text format : 99999999
How can I do that with a script ?

--------------------------------------------------------

I put the formatted numbers in various styles in column B from, and I used in cell C2 of Sheet1 my formula using the SPLIT function, and the SUBSTITUTE function  ...

yogi_SumAmountsForCategoriesFromDifferent Sheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

cstheday52 said:
I have a household budget workbook. It has 14 tabs, or separate sheets. I would like to Sum expenditures that have the same label, but are on a number of different sheets. Expenditures are in Column C (Amount) the label is in Column E (Location).
------------------------------------------
For this illustration, I used the data from only 3 sheets, but the solution can be used for any number of sheets. I used the VMERGE script by ahab to vertically merge the data in one thable and then I used the QUERY function to group the Amounts by Categories. The formula used in the Master sheet cell B1 is ...

=query(vmerge(Sheet1!C$1:E100,Sheet2!C$2:E100,Sheet3!C$2:E100),"select Col3,sum(Col1) where Col1 is not null group by Col3 label sum(Col1) 'Amount' ",1)

yogi_SearchRowForTextAndReturnContentsBelow

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

taffypaul said

Search row2 for text and return the contents of the cells below (and their next adjacent ones) without changing the structure of the data.

for example:
search for "feb" should return the following result...

ef,  gh
st,
dd,  ee

-------------------------------------------------

I used MATCH function to search the column number for feb, and then the QUERY function to pull the rest of the information of interest

yogi_ComputeHighestValueInColBForEveryDateInColA

                                         Google Spreadsheet   Post  #

                                                 (updated Oct-24-2015)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.  Feb-17-2011

Zero0nee said ...
I have column A and B. What I want to do is to create a new column C that contains the highest value in B for every one of the dates in A
---------------------------------------------------
So, let us go ...

Source data is in cells A2:B
my convoluted formula is in cell C1 (this one does need cleaning up and streamlining)

with Hearty Thanks to Andre aka ahab for clear insight,
my streamlined formula in cell F1 is :

=if(A2:A,mmult((A2:A=transpose(index(query(A2:B,"select A,max(B) where A is not null group by A label max(B) '' "),0,1)))*(transpose(index(query(A2:B,"select A,max(B) where A is not null group by A label max(B) '' "),0,2))),sign(row(indirect("A1:A"&count(unique(A2:A)))))),iferror(1/0))

yogi_PullingLatestRowOfDataFromSourceSpreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

GLukeQu said ...
I would like to export each row that is created from a form into it's own spreadsheet. I have attempted to import the data into a new spreadsheet but cannot figure how to pull the info automatically from the new row that the form creates. I can't figure out how to tell the new spreadsheet to pull info from the newest row (or a selected row) of the referenced spreadsheet.
---------------------------------------------------
So, let us go ...

For source data, I have created a spreadsheet
yogi_SourceSpreadsheet
and for pulling the data from the latest row or select row, I have created
yogi_PullingLatestRowOfDataFromSourceSpreadsheet

yogi_ConvertMultiDimensionalArrayIntoDataBaseTable

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

joshua.kuswadi said ...

How do I conditionally check a multi dimensional array and return a 1-dimensional result?

---------------------------------------------------
In the following solution I used the split function in cell B1 ... one formula should do it all, but because of a quirk in the split function where only first cell of the first row gets populated, I had to use one more formula in the cell C1 as delineated in the Result1 sheet, and in Result2 sheet I have used a single formula in cell B1 -- however in this case the first row is empty

yogi_RemoveBlankCellsInEachRow

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

macattack2401 said ...

How can I produce a sheet that doesn't have any empty cells.
I want each row just to show the actual entries that people have made. LIke a summary sheet that we can process easily.

---------------------------------------------------
In the following solution I used the concat operator '&' and the split function ... one formula should do it all, but because of a quirk in the split function where only first cell of the first row gets populated, I had to use one more formula in the second cell of the first row as delineated in the Summary sheet.

yogi_MergingDataFromDifferentSpreadsheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

MyList

CompanyList

Nathanael Emenaker said ...

"I want to be able to merge both of these lists and remove the duplicate names, so that the all my information remains, but the new names, the customers I have not contacted, are integrated into my list"

---------------------------------------------

in the solution presented in the following spreadsheet, I have used some intermediary calcs shown in gray colored text in the columns to the right

yogi_ClassCountByGradeAndGender

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

following is from Bliz2011 ... this is a sample of the columns I am working with. I need the command to count the number of 7th graders and put the total in one cell. Once I figure that out. I'm going to write one to count the 8th graders, males, and females. Thanks for your help.
--------------------------------------------------------------------------

In the following spreadsheet I have used the COUNTIF function as well as the QUERY function

yogi_ComputeWinningStockTrades

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

This is what gmadrone asked for ...
I have three columns of data. They are percentages of winning stock trades for a particular set of stocks
over three days. I am trying to calculate the number that reaches more than 2% at any time over the three
days. That means that if one goes over 2% on day one, but falls below 2% on day two, the total for day 2 should
still include that stock since it did exceed the limit at one point. The same is true for day 3, if a stock exceeds
2% on day 1 or day 2, but falls below 2% on day 3, I still want to count it in day 3 as a win. The link below shows the data and my current query to count the ones that have exceeded 2% on any particular day:

my formula in cell F16 is ...

=ArrayFormula(counta(iferror(filter($B4:$B14,($F4:$F14>2%)+(column()>=8)*($H4:$H14>2%)+(column()>=10)*($J4:$J14>2%)))))/counta($B4:$B14)

or

=ArrayFormula(counta(iferror(filter($B4:$B14,($F4:$F14>2%)+(column()>=columns($A:$H))*($H4:$H14>2%)+(column()>=columns($A:$J))*($J4:$J14>2%)))))/counta($B4:$B14)

formula in cell F16 is then copied to cells H16. and J16

yogi_ComputeHoursWorkedMidnightToMidnight

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yogi_Extracting Unique Values From A DataSet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

to extract the unique values input into the dataset, I use the Filter function as depicted in the following ...

yogi_QueryFunction -- Aggregating Numeric Data In A Field With Mixed Data Type

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

As shown in the following figure ...

data in Field2 is MIXED Numeric and Text. In the data as shown, there are 4 TEXT entries, 3 Numeric entries, and the rest are BLANK entries. In Google spreadsheet, QUERY function considers the data type of  a FIELD by its predominant data TYPE ... so in this particular case, the data TYPE of Field2 is TEXT.

If I want the QUERY function to operate on the numeric data in Field2, I will need to convert the TEXT entries in Field2 to NUMERIC entries. So, I have created a new set of date to be QUERIed as presented in cells E2:H12, by using the following formula in cell E2 ...

=ArrayFormula({"",A2:A,if(istext(B2:B),0,B2:B),C2:C})

In data set E2:H12, data in column F is all numeric, so it will lend itself to be AGGREGATED and operate on as necessary

We will look at various data extractions in the various sheets of the spreadsheet.